Qlik Community

Qlik Education Discussions

Discussion Board for collaboration related to Qlik Education.

Not applicable

Rolling Out YTD Average Sales

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

DepartmentIDMonthSalesYTD Avg Sales
201561/01/2014200.00 200.00
201561/02/2014300.00 250.00
201561/03/2014400.00 300.00
201561/04/2014500.00 350.00
201561/05/2014400.00 360.00
201561/06/2014450.00 375.00
201561/07/2014500.00 392.86
201561/08/2014600.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

1 Solution

Accepted Solutions

Re: Rolling Out YTD Average Sales

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;

2 Replies

Re: Rolling Out YTD Average Sales

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;

Not applicable

Re: Rolling Out YTD Average Sales

Thanks for your answer, Manish. I am trying your code

Community Browser