3 Months Rolling

[#1]

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

[rubenmarin]

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

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

[#3]

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

[rubenmarin]

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

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

PFA.

[SteveNewman]

‌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...