Calculating previous 12 months Avg in script in Qliksense

[bhargav_bhat]

Hi,

We have a requirement where we need to add a new field in the table which is nothing but the average of the previous 12 months

of sales.The sales data is present at the ProductID,ProductType,MonthName level. The data is as follows

Needed help to achieve this requirement

Regards,

Bhargav

[Anil_Babu_Samineni]

May be reload and use 1 in UI ??

[sunny_talwar]

Since your data begins from Apr Jan 2015, what value would you like to see for the first 11 months? Would it be null because they don't really have completed 12 months available, or will it display the average of however months are available to calculate average from?

[sunny_talwar]

And some other start at a later date... it might help, if you can share an Excel file with your expected output?

[bhargav_bhat]

Hi Sunny,

Thanks for replying

It should display average of however months available from the previous 12 months.For example , from the previous 12 months if 9 months are available then it should give average of the 9 months

Regards

Bhargav

[sunny_talwar · Accepted solution]

Check attached

Table:

LOAD

    ProductID,

    "Product Type",

    Date(Date#("MonthName", 'MMM YYYY'), 'MMM YYYY') as MonthName,

    MonthID,

    Sum(Sales) as Sales

FROM [lib://Desktop/previous 12 months avg.xlsx]

(ooxml, embedded labels, table is Sheet1)

Group By ProductID, "Product Type", "MonthName", MonthID;

FOR i = 1 to 11

Left Join (Table)

    LOAD ProductID,

    "Product Type",

         MonthID + $(i) as MonthID,

         Sales as Sales$(i)

    Resident Table;

NEXT i;

FinalTable:

LOAD ProductID,

    "Product Type",

    MonthName,

    MonthID,

    Sales,

    RangeAvg(Sales, Sales1, Sales2, Sales3, Sales4, Sales5, Sales6, Sales7, Sales8, Sales9, Sales10, Sales11) as [12MonthAvgSales]

Resident Table;

DROP Table Table;