3 Replies Latest reply on Jul 31, 2018 2:46 PM by Kathryn Mclaren

# Want to create a rolling 12 month viz for sales with comparatives to the previous 12 months

I want to be able to compare the current a rolling 12 months of sales and compare it to the rolling 12 months of sales for the prior period.  I know how to do it for the current period using this formula:

IF (DATEDIFF('month',[Invoice Date],TODAY()))<=12

THEN [Net  Adj. Extended Sale (\$)]

ELSE null

END

But unsure how to do it for the prior period.  Any help would be appreciated.

Kathryn

• ###### 1. Re: Want to create a rolling 12 month viz for sales with comparatives to the previous 12 months

Kathryn,

I'm not sure if this quite got there, but please see if the

below could give some ideas.

It took quite a few steps. Likely there are easier ways:

[Monthly Sales Current 12]:

IF DATEDIFF('month',[Order Date (Months)],[Today Parameter])<=12

AND DATEDIFF('month',[Order Date (Months)],[Today Parameter])>=0

THEN [Sales] END

// between this month and 12 months ago

[Rolling 12 Current]:

IF ATTR([Order Date (Months)])<=[Today Parameter] THEN

WINDOW_SUM(SUM([Monthly Sales Current 12]),-12,0)

END

// this month and before, add up rolling 12 months

[Monthly Sales Prev 12]:

IF DATEDIFF('month',[Order Date (Months)],[Today Parameter])<=24

AND DATEDIFF('month',[Order Date (Months)],[Today Parameter])>=12

THEN [Sales] END

// between 12 months before and 24 months before

[Rolling 12 Prev]:

IF ATTR([Order Date (Months)])<=DATEADD('month',-12,[Today Parameter]) THEN

WINDOW_SUM(SUM([Monthly Sales Prev 12]),-12,0)

END

[Difference]:

WINDOW_MAX([Rolling 12 Current])-WINDOW_MAX([Rolling 12 Prev])

• ###### 2. Re: Want to create a rolling 12 month viz for sales with comparatives to the previous 12 months

I haven't tested this, but something close to this should work for your "last year" measure:

THEN [Net  Adj. Extended Sale (\$)]

ELSE null

END

I'm also assuming you mean last year when you say prior period, if it's just a month then adjust the DATEADD appropriately.

• ###### 3. Re: Want to create a rolling 12 month viz for sales with comparatives to the previous 12 months

Thanks Swaroop, that worked!