expression

[upaliwije]

Hi

I have following pivot tbale in my QV Doc.

YEAR   PREMIUM    INCREASE

2013       5,710

2014       6,022           312

2015       6,556           534

2016       6,482            -74

2017       8,583          2,101

I have in my Pivot table only 1st two columns and I want to add the 3rd Coloum where the gap of premium between years are shown. Kindly help me with expression for the 3rd column. My expression for the Premium column is as follwos

SUM({<CLA_CODE={'SS'}>}PREMIUM)/SUM(TOTAL_MEMBERS)

[MK_QSL]

Dimension

YEAR

Expression

SUM(PREMIUM)

SUM(PREMIUM) - Above(SUM(PREMIUM))

Make sure you sort your table by YEAR

[tresesco]

Column(1) - Above(Column(1))

[upaliwije]

Thank you both of you. and I want to add further Coloumn Increase  as shown below. Kindly help me for that also

YEAR	PREMIUM	INCREASE	Cum Increa
2013	5,710
2014	6,022	312	312
2015	6,556	534	846
2016	6,482	-74	772
2017	8,583	2,101	2,873

[MK_QSL · Accepted solution]

RangeSum(Above(SUM(PREMIUM) - Above(SUM(PREMIUM)),0,RowNo()))

[upaliwije]

Thanks Manish 

I tried with my expression shown below but resuls not waht I expect . Kindly correct my expression given below

RangeSum(Above(SUM({<CLA_CODE={'SS'}>}PREMIUM))/SUM(TOTAL_MEMBERS)-above(SUM({<CLA_CODE={'SS'}>}PREMIUM))/SUM(TOTAL_MEMBERS),0,RowNo())

[MK_QSL]

RangeSum(Above(SUM({<CLA_CODE={'SS'}>}PREMIUM))/SUM({<CLA_CODE={'SS'}>}TOTAL_MEMBERS)-above(SUM({<CLA_CODE={'SS'}>}PREMIUM))/SUM({<CLA_CODE={'SS'}>}TOTAL_MEMBERS),0,RowNo())