Rolling based last three months average sale

[andymanu]

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.

Thanks a lot in advance.

Kind regards,

Andy

[agigliotti]

what's your expected output?

[rangam_s]

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.

[sunny_talwar]

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

[andymanu]

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_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

[ramkrishna86]

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

[pablolabbe]

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.

[Anonymous · Accepted solution]

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]

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]

Hi Ravichandra,

Thank you so much for your feedback.

It's giving the expected outcome.

Thank you again.

Regards,

Andy