Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

andymanu
Contributor

Rolling based last three months average sale

Hi,

I want to calculate the last three month's (if the current month is "June" the last three months should be March, April & May) average sales.

Please find the below table for the data set.

   

Sales
Prod_IDProd_NameJanuaryFebruaryMarchAprilMayJune
P100A100120110112130120
P101B506045304250
P102C115130125120110130
P104D200220230200190210
P105E606575486055
P106G105120125130110140
P107H909511085120100

I simply loaded the data in to Qlik View and thought of using a measure to calculate the average sales of the last three months and adding it as a new column, however, I am finding it bit tricky at the moment.

I am wondering whether I can use the "rangeavg" function to fulfill my said requirement?

Appreciate if someone could help me with this issue.

Thanks a lot in advance.

Kind regards,

Andy

1 Solution

Accepted Solutions
ravic906
Contributor III

Re: Rolling based last three months average sale

Hi!

Use this:

Temp:

LOAD

    Prod_ID,

    Prod_Name,

    January,

    February,

    March,

    April,

    May,

    June

FROM [lib://Community/308337.xlsx]

(ooxml, embedded labels, table is Sheet1);

CrossTable:

CrossTable(Month,value,2)

load *

Resident

Temp;

let var1=peek('Month',-1);

let var2=date(AddMonths(date#(var1,'MMMM'),-1),'MMMM');

let var3=date(AddMonths(date#(var1,'MMMM'),-2),'MMMM');

let var4=date(AddMonths(date#(var1,'MMMM'),0),'MMMM');

drop Table CrossTable;

Final:

LOAD *, round(RangeAvg($(var2),$(var3),$(var4))) as rangeavg

Resident Temp;

drop table Temp;

-RC

10 Replies
agigliotti
Honored Contributor II

Re: Rolling based last three months average sale

what's your expected output?

rangam_s
Contributor II

Re: Rolling based last three months average sale

Try this,

RangeAvg(Before(sum({<Year={$(=Max(Year))}>}[Sales Quantity]),1,3))

But it will show Avg for all the months when compared to previous 3 months since your using Pivot Table.

MVP
MVP

Re: Rolling based last three months average sale

Are you planning to use Month or MonthYear as dimension where you want to display the average of the last three months?

andymanu
Contributor

Re: Rolling based last three months average sale

Hi,

Basically, I need to calculate the last three months average sales since I am using it to predict the upcoming month's sales figure.

Please find the below table for more information.

    

Sales
Prod_IDProd_NameJanuary-18February-18March-18April-18May-18June-18Expected Outcome for JULY-18
P100A100120110112130120121
P101B50604530425041
P102C115130125120110130120
P104D200220230200190210200
P105E60657548605554
P106G105120125130110140127
P107H909511085120100102

Thus, the sales figures for last six months(January-18 to June-18) I have provided and my intention is to calculate the average of last three months sales (assuming the current month is June and the  average of April, May and June is the forecasted sales for the month July).

Thus, I am basically calculating the "Expected Outcome for JULY" column data.

Note

I am also planning to use the incremental data load to add the upcoming month's data.

Also I will be using the Month-Year format to in the table to represent the respective sales values and thanks Sunny for your comment.

Look forward to hearing from you.

Thank you all.

Kind regards,

Andy

ramkrishna86
Contributor II

Re: Rolling based last three months average sale

You can use rangesum() function to calculate rolling 3 month avg. in new column.

pablolabbe
Valued Contributor II

Re: Rolling based last three months average sale

Don't forget,


When applicable please mark the appropriate replies as CORRECT https://community.qlik.com/docs/DOC-14806. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

ravic906
Contributor III

Re: Rolling based last three months average sale

Hi!

Use this:

Temp:

LOAD

    Prod_ID,

    Prod_Name,

    January,

    February,

    March,

    April,

    May,

    June

FROM [lib://Community/308337.xlsx]

(ooxml, embedded labels, table is Sheet1);

CrossTable:

CrossTable(Month,value,2)

load *

Resident

Temp;

let var1=peek('Month',-1);

let var2=date(AddMonths(date#(var1,'MMMM'),-1),'MMMM');

let var3=date(AddMonths(date#(var1,'MMMM'),-2),'MMMM');

let var4=date(AddMonths(date#(var1,'MMMM'),0),'MMMM');

drop Table CrossTable;

Final:

LOAD *, round(RangeAvg($(var2),$(var3),$(var4))) as rangeavg

Resident Temp;

drop table Temp;

-RC

andymanu
Contributor

Re: Rolling based last three months average sale

Hi  Pablo,

Yes you are correct. However, until 20th July 2018 I did not get a working answer.

Thanks for many community members who provided valuable pathways to accomplish the solution.

Generally, if I get a query resolved, I'll mark them as correct.

However, I am yet to try Ravichandra's solution and will provide the feedback accordingly.

Thanks.

Andy

andymanu
Contributor

Re: Rolling based last three months average sale

Hi Ravichandra,

Thank you so much for your feedback.

It's giving the expected outcome.

Thank you again.

Regards,

Andy