topic Re: Calculate Above values in QlikView

Calculate Above values

Sat, 26 Jul 2014 14:57:32 GMT

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.

Re: Calculate Above values

MK_QSL — Sat, 26 Jul 2014 20:20:23 GMT

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;

Re: Calculate Above values

Sun, 27 Jul 2014 10:51:28 GMT

Thank you very much, it works.