topic Rolling Months Analysis in QlikView

Rolling Months Analysis

tresB — Fri, 17 Dec 2010 11:42:03 GMT

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

Rolling Months Analysis

tresB — Mon, 20 Dec 2010 06:08:33 GMT

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

Rolling Months Analysis

Mon, 20 Dec 2010 09:29:15 GMT

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

Rolling Months Analysis

tresB — Mon, 20 Dec 2010 09:57:35 GMT

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

Rolling Months Analysis

Mon, 20 Dec 2010 14:08:38 GMT

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;

Rolling Months Analysis

tresB — Tue, 21 Dec 2010 05:41:25 GMT

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

Rolling Months Analysis

Tue, 21 Dec 2010 06:55:52 GMT

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?

Rolling Months Analysis

tresB — Tue, 21 Dec 2010 07:08:02 GMT

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

Rolling Months Analysis

Tue, 21 Dec 2010 07:21:51 GMT

Hi tresesco,

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

Rolling Months Analysis

marcel_olmo — Tue, 21 Dec 2010 12:12:57 GMT

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!!!

Re: Rolling Months Analysis

Mon, 01 Dec 2014 11:38:06 GMT

Hi Tresesco,

I was trying to use the formula mentioned by you, but somehow not getting the correct result by slight difference.

Just confirming for the variable v24LessMonths, is it something like this addmonths(Date,-24)?

Thanks,

Neelam

Re: Rolling Months Analysis

tresB — Mon, 01 Dec 2014 11:46:34 GMT

Hi,

I even don't remember that I had a post like that. It has been 4 years now. I don't recollect it exactly, but it seems to be so.

Re: Rolling Months Analysis

Mon, 01 Dec 2014 11:52:29 GMT

lol...ok ya I realized later after posting that it was in 2010

Facing the same issue, so searched and got your post out of result.....

Thanks for the reply anyways...