Announcements
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.

4 Replies
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.

Contributor III

Is there a way out using this script, without the INLINE statement? What if the data is a much larger set coming directly from source?

Specialist II

Hi,

The first load statement can act as a preceding load to either data loaded previously or an external load, the INLINE is just an example based off what you provided.

What is important is that your source data is sorted correctly, so the INLINE was already.

This will add to the load time, so if running across lots of data that may become a problem. It is possible to do cumulative stuff in charts ... so if this is just one bit of the application and not used a lot you could take the performance hit there, you would need to experiment.

Cheers,

Chris.

Contributor III

thanks for your reply. I think maybe the sorting is proving to be difficulty, but also, the cumulative sum as only the most recent record is being included when I use your above code.

My problem is further outlined here; https://community.qlik.com/t5/New-to-Qlik-Sense/Cumulative-sum-in-script/td-p/1863987

Community Browser