12 month rolling does not work . Please Help!!!!

[#1]

Hello Everyone,

I have an issue with trying to display line chart for 12 month rolling. Basically i need to display last 12 month of sales .

For current month i need current month -1 ( we need last month as the Max) and 12 month back.

1. I'm using line chart

2. Measure is SalesDollars

3. I have fields such as: Year_MonthText ( example: 2014-Jan ) , Year_Month ( example: 2014-01) , OrderYear , OrderMonth.

Please help i tried searching for solution and tried every solution out there for some reason graph display one dot and with no sales. It just doesn't work.

.

Here is what i tried.

SUM({$<Year_MonthText = {">=$(=(Max(Year_MonthText)-11))<=$(=Max(Year_MonthText)-1)"} =>}SalesDollars)        ---did not work

Sum({$<MonthYear = {“>=$(=Max(MonthYear) – 11)<=$(=Max(MonthYear)-1)”},OrderYear= ,OrderMonth = >} SalesDollars) ---did not work

---------------------

But if i use year field WHICH I DO NOT NEED for this project it Works

Sum({<OrderYear= {'<=$(=Max(OrderYear)) >=$(=Max(OrderYear)-1) '}>} SalesDollars)   -- Works but i dont need to use year i need 12 month calculation .

Please help !!! Thank you so much in advance

[jvitantonio]

Hi,

you can subtract a number to a string (ex: Jan-2015 - 11  <---- This doesn't not exist).

For YearMonth, if this is in the format of 201507 (July 2015) this won't work either: 201507 - 11  = 201496 <- this date doesn't exist.

Instead, create a sequential month number in the script like the following:

Load *, 12 * YEARFIELD + MONTHFIELD as SeqMonth;

YearField and MonthField should be numbers.

This will create a sequential and unique number for each month of the year. You can the do this:

Sum({<SeqMonth= {">=$(=Max(SeqMonth))  - 11)"}>} SalesDollars) 

JV

[#3]

Hi ,

Thank you for the reply. I tried to do that and it still didnt work i got one dot on chart with no value (0 sales) .

This is what i got in my SeqMonth field after doing 12 * YEARFIELD + MONTHFIELD as SeqMonth

I used following script for measure in chart Sum({<SeqMonth= {">=$(=Max(SeqMonth))  - 11)"}>} SalesDollars)

[jvitantonio]

Try changing your expression to this:  Sum({<SeqMonth= {">=$(=Max(SeqMonth)- 11)"}>} SalesDollars)

[#5]

This is Great!!! It worked perfectly!

Is it possible with this approach to exclude current month? I want it to be previous month and 12 months back. Thank you!

[jvitantonio · Accepted solution]

Sum({<SeqMonth= {">=$(=Max(SeqMonth)- 11) < $(=Max(SeqMonth))"}>} SalesDollars)

[#7]

Thank you !!! I really appreciate your help.