Qlik Community

Ask a Question

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

1 Reply
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