Rolling 12 Months Analysis

[tresesco]

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

[#2]

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.

[tresesco]

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

[lkallioniemi]

Hi,

you can try something like this:

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

BR

Lasse

[#5]

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.

[tmumaw]

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

[tresesco]

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