Calculate Above values

[#1]

Dear Qlikview Users,

I have one tough situation in developing a qlikview dashboard, that is how to calculate sales of New York in the past months? Please find the sample data below:

City	Month	Sales	Avg Above Sales
NewYork	2013/7/1	1000	1000
NewYork	2013/8/1	1200	1100
NewYork	2013/9/1	1500	1233.333333
NewYork	2013/10/1	2000	1425
NewYork	2013/11/1	3000	1740
NewYork	2013/12/1	1800	1750
NewYork	2014/1/1	1200	1671.428571

The last column, always calculate the average sales amount from 2013-07-1 to current month.

I really have no idea, can you please help on this? Many thanks.

[MK_QSL · Accepted solution]

Use below Script...

Sales:

Load

  City,

  Date(Date#(Month,'YYYY/M/D')) as Month,

  Sales

Inline

[

  City, Month, Sales

  NewYork, 2013/7/1, 1000

  NewYork, 2013/8/1, 1200

  NewYork, 2013/9/1, 1500

  NewYork, 2013/10/1, 2000

  NewYork, 2013/11/1, 3000

  NewYork, 2013/12/1, 1800

  NewYork, 2014/1/1, 1200

];

NoConcatenate

Final:

Load

  RowNo() as NO,

  City,

  Month,

  Sales,

  RangeSum(Peek(TotalSales),Sales) as TotalSales,

  RangeSum(Peek(TotalSales),Sales)/RowNo() as AvgSales 

Resident Sales

Order By City, Month;

Drop Table Sales;

[#3]

Thank you very much, it works.