topic Re: 12 month rolling does not work . Please Help!!!! in Visualization and Usability

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

Tue, 24 Feb 2015 13:39:15 GMT

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

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

jvitantonio — Tue, 24 Feb 2015 16:28:09 GMT

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)

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

Tue, 24 Feb 2015 16:42:34 GMT

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)

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

jvitantonio — Tue, 24 Feb 2015 16:48:20 GMT

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

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

Tue, 24 Feb 2015 17:05:12 GMT

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!

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

jvitantonio — Tue, 24 Feb 2015 17:15:53 GMT

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

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

Tue, 24 Feb 2015 17:17:27 GMT

Thank you !!! I really appreciate your help.