10 Replies Latest reply: Jul 29, 2018 1:45 AM by Andy Manuja

# 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_ID Prod_Name January February March April May June P100 A 100 120 110 112 130 120 P101 B 50 60 45 30 42 50 P102 C 115 130 125 120 110 130 P104 D 200 220 230 200 190 210 P105 E 60 65 75 48 60 55 P106 G 105 120 125 130 110 140 P107 H 90 95 110 85 120 100

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.

Kind regards,

Andy

• ###### 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.

• ###### 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?

• ###### 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.

 Sales Prod_ID Prod_Name January-18 February-18 March-18 April-18 May-18 June-18 Expected Outcome for JULY-18 P100 A 100 120 110 112 130 120 121 P101 B 50 60 45 30 42 50 41 P102 C 115 130 125 120 110 130 120 P104 D 200 220 230 200 190 210 200 P105 E 60 65 75 48 60 55 54 P106 G 105 120 125 130 110 140 127 P107 H 90 95 110 85 120 100 102

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

• ###### Re: Rolling based last three months average sale

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

• ###### 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.

• ###### 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

• ###### Re: Rolling based last three months average sale

Hi!

Use this:

Temp:

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)

Resident

Temp;

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

drop Table CrossTable;

Final:

Resident Temp;

drop table Temp;

-RC

• ###### 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

• ###### Re: Rolling based last three months average sale

Hi Fawaz,

I did according to what you said but it calculates the total cost of each Project Component for the given months, not the annualised cost amount?

Also would like to know the use of "ColumnNo()=0" expression?

Thank you.

Kind regards,

Andy