Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
twanqlik
Creator
Creator

Script: cumulative sum + group by

DateMarketFactorValueCumulative per Date and Market
MarchNAM15050
MarchNAM32575
MarchNAM121085
MarchAFI5200200
MarchAFI9100300
MarchAFI175375
AprilNAM10500500
AprilNAM11250750
AprilNAM3150900
AprilAFI89090
AprilAFI330120
AprilAFI110130

 

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
chrismarlow
Specialist II
Specialist II

Hi,

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

data:
Load
	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],
	*;
LOAD * Inline [
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.

 

View solution in original post

4 Replies
chrismarlow
Specialist II
Specialist II

Hi,

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

data:
Load
	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],
	*;
LOAD * Inline [
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.

 

MAPSF1
Contributor III
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? 

chrismarlow
Specialist II
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.

MAPSF1
Contributor III
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