Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

bhargav_bhat
Contributor II

Calculating previous 12 months Avg in script in Qliksense

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

image.PNG

Needed help to achieve this requirement

Regards,

Bhargav

Tags (1)
1 Solution

Accepted Solutions

Re: Calculating previous 12 months Avg in script in Qliksense

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;

5 Replies

Re: Calculating previous 12 months Avg in script in Qliksense

May be reload and use 1 in UI ??

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

Re: Calculating previous 12 months Avg in script in Qliksense

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?

Re: Calculating previous 12 months Avg in script in Qliksense

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

bhargav_bhat
Contributor II

Re: Calculating previous 12 months Avg in script in Qliksense

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

Re: Calculating previous 12 months Avg in script in Qliksense

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;