Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a question.
Here's the scenario. I am trying to calculate the YTD Avg Sales per month at the data loading
DepartmentID | Month | Sales | YTD Avg Sales |
20156 | 1/01/2014 | 200.00 | 200.00 |
20156 | 1/02/2014 | 300.00 | 250.00 |
20156 | 1/03/2014 | 400.00 | 300.00 |
20156 | 1/04/2014 | 500.00 | 350.00 |
20156 | 1/05/2014 | 400.00 | 360.00 |
20156 | 1/06/2014 | 450.00 | 375.00 |
20156 | 1/07/2014 | 500.00 | 392.86 |
20156 | 1/08/2014 | 600.00 | 418.75 |
for Feb, YTD Avg Sales is the average of Sales per month from Jan - Feb;
for Aug, YTD Avg Sales is the average of Sales per month from Jan - Aug.
I tried RangeAvg(Above()), it works in charts but does not work in scripts.
Does anyone know how to do the coding?
Thanks.
Linda
Temp:
Load
DepartmentID,
Date(Date#(Month,'D/MM/YYYY')) as Month,
Sales
Inline
[
DepartmentID, Month, Sales
20156, 1/01/2014, 200.00
20156, 1/02/2014, 300.00
20156, 1/03/2014, 400.00
20156, 1/04/2014, 500.00
20156, 1/05/2014, 400.00
20156, 1/06/2014, 450.00
20156, 1/07/2014, 500.00
20156, 1/08/2014, 600.00
20157, 1/01/2014, 200.00
20157, 1/02/2014, 250.00
20157, 1/03/2014, 300.00
20157, 1/04/2014, 600.00
20157, 1/05/2014, 400.00
20157, 1/06/2014, 250.00
20157, 1/07/2014, 500.00
20157, 1/08/2014, 300.00
];
NoConcatenate
Load
*,
TotalSales/ID as AvgSales;
Load
*,
IF(DepartmentID <> Previous(DepartmentID), 1, Peek(ID) + 1) as ID,
IF(DepartmentID = Previous(DepartmentID), Peek(TotalSales) + Sales, Sales) as TotalSales
Resident Temp
Order By DepartmentID, Month;
Drop Table Temp;
Temp:
Load
DepartmentID,
Date(Date#(Month,'D/MM/YYYY')) as Month,
Sales
Inline
[
DepartmentID, Month, Sales
20156, 1/01/2014, 200.00
20156, 1/02/2014, 300.00
20156, 1/03/2014, 400.00
20156, 1/04/2014, 500.00
20156, 1/05/2014, 400.00
20156, 1/06/2014, 450.00
20156, 1/07/2014, 500.00
20156, 1/08/2014, 600.00
20157, 1/01/2014, 200.00
20157, 1/02/2014, 250.00
20157, 1/03/2014, 300.00
20157, 1/04/2014, 600.00
20157, 1/05/2014, 400.00
20157, 1/06/2014, 250.00
20157, 1/07/2014, 500.00
20157, 1/08/2014, 300.00
];
NoConcatenate
Load
*,
TotalSales/ID as AvgSales;
Load
*,
IF(DepartmentID <> Previous(DepartmentID), 1, Peek(ID) + 1) as ID,
IF(DepartmentID = Previous(DepartmentID), Peek(TotalSales) + Sales, Sales) as TotalSales
Resident Temp
Order By DepartmentID, Month;
Drop Table Temp;
Thanks for your answer, Manish. I am trying your code