7 Replies Latest reply on Oct 24, 2019 8:52 PM by Zhouyi Zhang

# Calculation for a Year-over-Year formula

I am trying to think of a formula to calculate sales number year over year.

ideally, if i am reporting the sales number for jan 15 2019, i want it to comp against whatever the number was 365 days ago, in this case it's jan 15 2018.

Here's an idea of how my formula will look like (it's incorrect format/syntax, of course.)

[ if(date = today then sales) - if(date = 365 days then sales) ] / if(date = 365 days then sales)

• ###### 1. Re: Calculation for a Year-over-Year formula

Hi, Nicholas

Try below calculation

sum([Date] = Today() then [Sales] end)

/

sum([Date] = Dateadd('year',-1,Today()) then [Sales] end)

-1

ZZ

1 of 1 people found this helpful
• ###### 2. Re: Calculation for a Year-over-Year formula

Hi Zhouyi,

inputting this into the calculated field gives me the 'expected closing parentheses or comma while parsing argument list for sum'

sum([Date] = Today() then [Sales] end)

1 of 1 people found this helpful
• ###### 3. Re: Calculation for a Year-over-Year formula

Hi, Nicholas

my bad, I forget add the "IF", below is the fixed calculation, let me know if any further issue

sum(If [Date] = Today() then [Sales] end)

/

sum(If [Date] = Dateadd('year',-1,Today()) then [Sales] end)

-1

ZZ

1 of 1 people found this helpful
• ###### 4. Re: Calculation for a Year-over-Year formula

hi zhouyi,

the formula is perfect! another question though how do I handle this with aggregated calculations when calculating sales at a per unit level?

sum(If [Date] = Today() then sum([Sales])/sum([Units]) end)

/

sum(If [Date] = Dateadd('year',-1,Today()) then sum([Sales])/sum([Units]) end)

-1

1 of 1 people found this helpful
• ###### 5. Re: Calculation for a Year-over-Year formula

Hi, Nicholas

probably try this

{Fixed [Units]:sum(If [Date] = Today() then [Sales] end) }

/

{Fixed [Units]:sum(If [Date] = Dateadd('year',-1,Today()) then [Sales] end)}

-1

ZZ

2 of 2 people found this helpful
• ###### 6. Re: Calculation for a Year-over-Year formula

hey zhouyi,

I finally figured out a way to bypass adding an aggregated formula:

// this is how to calculate per units (aggregates)

(

(sum(If [Date] = DATEADD('day',-1,today()) then [Gross GP] end) / sum(If [Date] = DATEADD('day',-1,today()) then [Units] end))

-

(sum(If [Date] = DATEADD('day',-2,today()) then [Gross GP] end) / sum(If [Date] = DATEADD('day',-2,today()) then [Units] end))

)

/

(sum(If [Date] = DATEADD('day',-2,today()) then [Gross GP] end) / sum(If [Date] = DATEADD('day',-2,today()) then [Units] end))

1 of 1 people found this helpful
• ###### 7. Re: Calculation for a Year-over-Year formula

Cool~~

ZZ

1 of 1 people found this helpful