QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for
Did you mean:
Creator

Script: cumulative sum + group by

 Date Market Factor Value Cumulative per Date and Market March NAM 1 50 50 March NAM 3 25 75 March NAM 12 10 85 March AFI 5 200 200 March AFI 9 100 300 March AFI 1 75 375 April NAM 10 500 500 April NAM 11 250 750 April NAM 3 150 900 April AFI 8 90 90 April AFI 3 30 120 April AFI 1 10 130

I'm trying to calculate the field: Cumulative per Date and Market.

I tried with the following line, but it doesn't work:

if(Market = Previous(Market), RangeSum(Value, Peek('Pareto_Negative_Cumulative')), Value) AS Pareto_Negative_Cumulative,

Order by Date, Value,Market desc;

Any ideas? 🙂

1 Solution

Accepted Solutions
Specialist II

Hi,

I think you need to use Peek and will have to test on the first row, so try;

``````data:
If(RowNo()=1,
Value,
If(Date=Peek(Date) and Market=Peek(Market),
Peek([Cumulative per Date and Market])+Value,
Value
)
)  AS [Cumulative per Date and Market],
*;
Date,	Market,	Factor,	Value
March,	NAM,	1,	50
March,	NAM,	3,	25
March,	NAM,	12,	10
March,	AFI,	5,	200
March,	AFI,	9,	100
March,	AFI,	1,	75
April,	NAM,	10,	500
April,	NAM,	11,	250
April,	NAM,	3,	150
April,	AFI,	8,	90
April,	AFI,	3,	30
April,	AFI,	1,	10
];``````

Cheers,

Chris.

Specialist II

Hi,

I think you need to use Peek and will have to test on the first row, so try;

``````data:
If(RowNo()=1,
Value,
If(Date=Peek(Date) and Market=Peek(Market),
Peek([Cumulative per Date and Market])+Value,
Value
)
)  AS [Cumulative per Date and Market],
*;
Date,	Market,	Factor,	Value
March,	NAM,	1,	50
March,	NAM,	3,	25
March,	NAM,	12,	10
March,	AFI,	5,	200
March,	AFI,	9,	100
March,	AFI,	1,	75
April,	NAM,	10,	500
April,	NAM,	11,	250
April,	NAM,	3,	150
April,	AFI,	8,	90
April,	AFI,	3,	30
April,	AFI,	1,	10
];``````

Cheers,

Chris.