topic Re: 3 Months Rolling in QlikView

3 Months Rolling

Sun, 08 Oct 2017 12:43:27 GMT

Hi All,

I have problem. below is sample aggregated data but I want transaction data in dashboard.

Data:

LOAD

If(RecNo()<=6,'India','Japan') as Country,

Date1,

Date2

INLINE [

Date1, Date2, Sales

201701, 201611, 15

201701, 201612, 25

201701, 201701, 35

201702, 201612, 45

201702, 201701, 55

201702, 201702, 95

201701, 201611, 10

201701, 201612, 20

201701, 201701, 30

201702, 201612, 40

201702, 201701, 50

201702, 201702, 90

];

I am expecting that always one value selected in RD. Below pivot table is expected(Always one value selected in Date1).

Date1

Country 201701 201702

India 25 65

Japan 20 60

Explanation : 25 for 201701 for India is average of 15,25,35.

20 for 201701 for Japan is average of 10,20,30.

But this is aggregated data I have day level data I want transaction data in dashboard. Don't want to aggregate in script.

This means I have like 50 to 500 records for each month for Date2 I have shown you aggregated data here to practice but it transaction data.

Thanks

Swathi

Re: 3 Months Rolling

rubenmarin — Sun, 08 Oct 2017 15:33:47 GMT

Hi Swathi, to do the aggregation you can use an Aggr() that aggregates the value before doing the AVG:

Avg(Aggr(Sum(Sales), Date2))

Re: 3 Months Rolling

Sun, 08 Oct 2017 17:07:46 GMT

Hi Ruben,

Thanks for replying. I tried but it shows correct for first yearmonth i.e. for 201701 but for 201702 it is showning only for max month only.

I tried Avg({<Date1>}Aggr(Sum({<Date1>}Sales),Date2,Country))

Regards

Swathi

Re: 3 Months Rolling

rubenmarin — Mon, 09 Oct 2017 10:23:08 GMT

Hi Swathi, I'm not sure of what you are trying, maybe adding Country to the aggr?

Avg(Aggr(Sum(Sales), Date2, Country))

PFA.

Re: 3 Months Rolling

SteveNewman — Mon, 09 Oct 2017 10:52:33 GMT

‌You may want to look at this example. It has multiple forecasting methods shown, including a rolling 3 month average.

GitHub - newmans99/Forecast-Sampler: An example Qlik Sense application that demonstrates forecasting capabilities.