Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
what's your expected output?
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.
Are you planning to use Month or MonthYear as dimension where you want to display the average of the last three months?
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
You can use rangesum() function to calculate rolling 3 month avg. in new column.
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.
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
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
Hi Ravichandra,
Thank you so much for your feedback.
It's giving the expected outcome.
Thank you again.
Regards,
Andy