topic Re: Cumulative Value in QlikView

Cumulative Value

upaliwije — Mon, 28 Aug 2017 09:32:05 GMT

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.

Re: Cumulative Value

effinty2112 — Mon, 28 Aug 2017 09:42:36 GMT

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

Re: Cumulative Value

techvarun — Mon, 28 Aug 2017 09:42:44 GMT

share sample data/qvw to work on this

Re: Cumulative Value

upaliwije — Mon, 28 Aug 2017 09:51:26 GMT

Thanks Andrew

I tried but It does not work

Re: Cumulative Value

Kushal_Chawda — Mon, 28 Aug 2017 09:52:38 GMT

please share the sample file

Re: Cumulative Value

antoniotiman — Mon, 28 Aug 2017 09:54:36 GMT

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

Re: Cumulative Value

effinty2112 — Mon, 28 Aug 2017 09:58:27 GMT

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