topic Rolling 12 Months Analysis in QlikView

Rolling 12 Months Analysis

tresB — Thu, 16 Dec 2010 10:55:00 GMT

Dear All,

I want 12 rolling months analysis with Months on the dimension. i.e.

Dimension Expression

2010 Dec Data of (2010 Dec to 2010 Jan)

2010 Nov Data of(2010 Nov to 2009 Dec)

.....

2010 Jan Data of(2010 jan to Feb 2009)

is this possible in front end?

please help.

Thanks & Regards,

tresesco

Rolling 12 Months Analysis

Thu, 16 Dec 2010 11:18:45 GMT

Hi Tresesco,

You can try the following:

rangeavg(above(Sum(Amount),0,12))

In this example the 12 represents the number of periods, in your case months, that will be used for your calculation.

Good luck.

Rolling 12 Months Analysis

tresB — Thu, 16 Dec 2010 13:44:55 GMT

Thanks Keller for your reply. but your formula gives the right output for the last Month only in Bar chart, because it is using the accumulation concept.

Can you please try for another time?

Regards,

tresesco

Rolling 12 Months Analysis

lkallioniemi — Thu, 16 Dec 2010 13:58:10 GMT

Hi,

you can try something like this:

aggr(Rangeavg(above(sum({$<Year=, Month=> } Amount ),0,12)),Month)

Lasse

Rolling 12 Months Analysis

Thu, 16 Dec 2010 16:47:23 GMT

Can you explain what the difference is between avg and rangeavg? I've tried using both and rangeavg either doesn't work for me or doesn't do what I want.

Rolling 12 Months Analysis

tmumaw — Thu, 16 Dec 2010 17:47:09 GMT

Tresesco,

You could build it into your date routine something like this

FiscalCalendar:
LOAD FISPD,
FISYR,
[Billing Date] as [Rept Date],
WEDAT,
WKNO,
if([Billing Date] <= '$(vToday)' AND FISPD <= '$(vFisPd)' AND WKNO <= '$(vWkNo)', 1) AS YTDFlag,
if([Billing Date] <= '$(vToday)' AND FISPD = '$(vPFisPd)', 1) AS MTDFlag,
if([Billing Date] <= '$(vToday)' AND [Billing Date] >= '$(vPrevToday)', 1) AS RTDFlag
FROM [D:\Qlikview\New Sales\QVD\ZZZWEDAT (qvd)
where FISYR >= '$(vPrevYear)' and FISYR < '$(vYear)';

Then use set analysis to check your RTDFlag.

Hope this helps.

Thom

Rolling 12 Months Analysis

tresB — Fri, 17 Dec 2010 07:51:42 GMT

Thanks All for your inputs in this post. I have cracked it . you van have a look at what i did (for only Rolling 6 MONTHS... can be extended peek function fo 12 MONTHS or so.) :

Raw:

Load*Inline

[Claim, Status, Date,Amount

;

Temp0:

Load

Month(Date) asMonth,

MonthName(Date) asMonthName,

Year(Date) asYear,

YearName(Date,4) asFiscalYear

ResidentRaw;

Temp:

Load

Sum(Amount) asMonthlyAmount1,

MonthName

residentTemp0GroupByMonthName;

Temp1:

//Noconcatenate

Load

MonthlyAmount1asMonthlyAmount,

MonthNameasMonthName1

ResidentTempOrderByMonthName;

Temp2:

Load

MonthlyAmountasFMA,

MonthName1asFinalMonthName,

RangeSum(MonthlyAmount,Peek('FMA'),Peek('FMA',-2),Peek('FMA',-3),Peek('FMA',-4),

Peek('FMA',-5)) asRolling_6_Amount

ResidentTemp1;

DroptableTemp0,Temp,Temp1;

These repetitive Peek() could be avoided by a similar function of ABOVE() (at front end).

Regards,

tresesco