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.
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.
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.
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.
(ooxml, embedded labels, table is Sheet1);
drop Table CrossTable;
LOAD *, round(RangeAvg($(var2),$(var3),$(var4))) as rangeavg
drop table Temp;