Rolling Months Analysis

[tresesco]

Hi All,

I need to analyze data on 12 Rolling Months on Months itself on dimension. I used MonthName([Agent Month]) in Dimension and Expression as follows:

sum({$<[Agent Year]=,[Agent Month]=,[Agent FiscalYear]=,[Agent Date]={"<=$(vAgentMaxDate) >=$(vAgent12LessDate)"}>} Agent.NIC))

This gives me all rolling 12 months on dimension, BUT the SUM() gives "FOR THE MONTH" amount. i want the amount also for 12 Rolling Months, i.e

Dimesion Amount

2010Dec Data of (2010Dec TO 2010Jan)

2010Nov Data of (2010Nov TO 2009Dec)

2010Oct Data of (2010Oct TO 2009Nov)

....

2010Jan ....

Any Suggestion?

Thanks and Regards,

tresesco

[tresesco]

Hi All, Thanks for NO response . are you surprised ? don't be so. i really thank for No response. because this made me find my own solution...a tricky one. i have found a solution for the same to implement at front end. you are welcome .

One thing, please don't leave always to find my own solution ..... ha..ha.. 🙂

Regards,

tresesco

[#3]

If you found a good solotion, you could publish it for the benefit of other users, who have the same challenge. This would infact make the topic valueable.

Regards
Jens

[tresesco]

Hi Jens,

You are right. So let me explain what i did.

i have used two variables , vMaxDate and v24MonthsLess. then at the front end used the formula:

Rangesum(Below(Sum({<Date={'<=$(vMaxDate) >=$(v24LessMonths)'}>}),0,12))

Then sorted the dimension on numeric value (for MonthName(Date)) in descending order. This gives me my desired result, only with a limitation of sort order. The last 12 months gives me the perfect values, because the BELOW function calculates on accumulation of last 12 months. 12 extra i had to use because, the last rolling months needs to get back to previous 12 months amount.

at the presentation tab, make the maximum visible columns to 12.

Hope i have been able to explain my logic upto some extent.

Regards,

tresesco

[#5]

Hi,

Here is the another solution

I have used two variables which will have the min and max month...and month is in "YYYYMM" format...using the for loop I have calculated the rolling 3 months sales...

for i=$(V_Min_Month) to $(V_Max_Month)

Let V_Month_To=if(match(right($(i),2),01,02,03),$(i)-91,$(i)-3); //To find out the previous 3 months

if right($(i),2)<13 then

Table1:
LOAD
$(i) as YearMonth,
Sales
FROM
Sales.xls
(biff, embedded labels, table is Sample$)
Where Month<$(i)
and Month>=$(V_Month_To);

end if

Next i;

[tresesco]

Hi Bala,

Good to get another solution, but doing at backend would make this static, so i would suggest to do it at the front end to have the dynamic output (i.e - selection based). if i am wrong, please correct me.

Regards,

tresesco

[#7]

Hi,

If we calculate this rolling months at the front end then this calculation should be performed whenever the user changes the selections...But this will be calculated only once if we do it at the script level...

Rangesum(Below(Sum({<Date={'<=$(vMaxDate) >=$(v24LessMonths)'}>}),0,12))

Using this formula can you see any values if you select one month alone?

[tresesco]

Hi,

Certainly. If you select a month alone, it would calculate the maxdate accordingly and then starting from that month itself it would show all rolling 12 months. and yes for that you have to bypass the MONTH and YEAR in set analysis like:

Rangesum(Below(Sum({<Month=,Year=,Date={'<=$(vMaxDate) >=$(v24LessMonths)'}>}),0,12))

Hope this would help.

regards,

tresesco

[#9]

Hi tresesco,

We can add other fields also in the for loop so that the chart will give the dynamic output...

[marcel_olmo]

Thanks Tresesco for being interested in my post.

Finally I got my desired result, so I think it won't be necessary to look into your post.

Anyways, I've attached a demo of my final result. So you can compare both solutions.

See you around!!!