topic Re: Rolling Out YTD Average Sales in Qlik Learning Discussions

Rolling Out YTD Average Sales

Tue, 16 Sep 2014 07:31:21 GMT

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

Re: Rolling Out YTD Average Sales

MK_QSL — Tue, 16 Sep 2014 07:58:04 GMT

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;

Re: Rolling Out YTD Average Sales

Wed, 17 Sep 2014 00:29:15 GMT

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