Cumulative Value

[upaliwije]

Dear Friends

In my following Pivot table monthly figures are shown with growth rate

I want to calculate cumulative monthly figures in the same format as shown below ( That month of Feb should show Jan+Feb Figures)

My expression use for the above are as follows

Actual 2016    sum(PREMIUM)

Budget 2017   Sum(BUDGET)

GROWTH      SUM(BUDGET-PREMIUM)/SUM(PREMIUM)

Pls help me to to the above.

[effinty2112]

Hi Upali,

You try this as your expression:

Pick(

Match(MONM,'Actual-2016','Budget-2016','Growth'),

Sum(PREMIUM)

Sum(BUDGET)

Num(SUM(BUDGET-PREMIUM)/SUM(PREMIUM),'0%')

Good luck

Andrew

[techvarun]

share sample data/qvw to work on this

[upaliwije]

Thanks Andrew

I tried but It does not work

[Kushal_Chawda]

please share the sample file

[antoniotiman · Accepted solution]

Hi Upali,

try

RangeSum(Above(Sum(PREMIUM),0,RowNo()))

RangeSum(Above(Sum(BUDGET),0,RowNo()))

(RangeSum(Above(Sum(BUDGET),0,RowNo()))-RangeSum(Above(Sum(PREMIUM),0,RowNo())))/RangeSum(Above(Sum(PREMIUM),0,RowNo()))

[effinty2112]

Hi Upali,

The images you posted look like they came from Excel. I guess then your QV data model does not have the dimension MONM. If not you can add a calculated dimension:

MONM :

ValueList('Actual-2016','Budget-2016','Growth')

and use expression:

Pick(

Match(ValueList('Actual-2016','Budget-2016','Growth'),'Actual-2016','Budget-2016','Growth'),

sum(PREMIUM)

Sum(BUDGET)

SUM(BUDGET-PREMIUM)/SUM(PREMIUM))

Good luck

Andrew