Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
We have a requirement where we need to add a new field in the table which is nothing but the average of the previous 12 months
of sales.The sales data is present at the ProductID,ProductType,MonthName level. The data is as follows
Needed help to achieve this requirement
Regards,
Bhargav
Check attached
Table:
LOAD
ProductID,
"Product Type",
Date(Date#("MonthName", 'MMM YYYY'), 'MMM YYYY') as MonthName,
MonthID,
Sum(Sales) as Sales
FROM [lib://Desktop/previous 12 months avg.xlsx]
(ooxml, embedded labels, table is Sheet1)
Group By ProductID, "Product Type", "MonthName", MonthID;
FOR i = 1 to 11
Left Join (Table)
LOAD ProductID,
"Product Type",
MonthID + $(i) as MonthID,
Sales as Sales$(i)
Resident Table;
NEXT i;
FinalTable:
LOAD ProductID,
"Product Type",
MonthName,
MonthID,
Sales,
RangeAvg(Sales, Sales1, Sales2, Sales3, Sales4, Sales5, Sales6, Sales7, Sales8, Sales9, Sales10, Sales11) as [12MonthAvgSales]
Resident Table;
DROP Table Table;
May be reload and use 1 in UI ??
Since your data begins from Apr Jan 2015, what value would you like to see for the first 11 months? Would it be null because they don't really have completed 12 months available, or will it display the average of however months are available to calculate average from?
And some other start at a later date... it might help, if you can share an Excel file with your expected output?
Hi Sunny,
Thanks for replying
It should display average of however months available from the previous 12 months.For example , from the previous 12 months if 9 months are available then it should give average of the 9 months
Regards
Bhargav
Check attached
Table:
LOAD
ProductID,
"Product Type",
Date(Date#("MonthName", 'MMM YYYY'), 'MMM YYYY') as MonthName,
MonthID,
Sum(Sales) as Sales
FROM [lib://Desktop/previous 12 months avg.xlsx]
(ooxml, embedded labels, table is Sheet1)
Group By ProductID, "Product Type", "MonthName", MonthID;
FOR i = 1 to 11
Left Join (Table)
LOAD ProductID,
"Product Type",
MonthID + $(i) as MonthID,
Sales as Sales$(i)
Resident Table;
NEXT i;
FinalTable:
LOAD ProductID,
"Product Type",
MonthName,
MonthID,
Sales,
RangeAvg(Sales, Sales1, Sales2, Sales3, Sales4, Sales5, Sales6, Sales7, Sales8, Sales9, Sales10, Sales11) as [12MonthAvgSales]
Resident Table;
DROP Table Table;