topic Cumulative Calculation By each Row in QlikView

Cumulative Calculation By each Row

Mon, 16 Mar 2015 15:43:45 GMT

Greetings,

I would like to achieve cumulative calculation using pivot table.

Please find attach example(Spread Sheet) and sample model.

Re: Cumulative Calculation By each Row

MK_QSL — Mon, 16 Mar 2015 16:01:47 GMT

Use below script

LOAD *, Date(MakeDate(year,period),'MMM YYYY') as MonthYear Inline

[

year, period, product, exp1, exp2

2015, 1, a1, 100, 20

2015, 1, b1, 200, 0

2015, 2, a1, 200, 0

2015, 2, b1, 50, 10

2015, 3, a1, 40, 25

2015, 3, b1, 250, 50

];

Now create a Pivot Table

Dimension

product

MonthYear

Expression

SUM(exp1)

RangeSum(Above(SUM(exp1),0,RowNo()))

Same way use for exp2

Re: Cumulative Calculation By each Row

Roop — Mon, 16 Mar 2015 16:44:05 GMT

use:

if(Dimensionality()>0, if(aggr(min(period),year)>0, sum(exp1), sum(exp1) + rangesum(Before(Total Exp1YTD))), Sum(TOTAL exp1))

The attached file will give full details.

Re: Cumulative Calculation By each Row

Tue, 17 Mar 2015 10:00:40 GMT

Thanks for replying.

But I would like to see by each product not in total level.

Re: Cumulative Calculation By each Row

Roop — Tue, 17 Mar 2015 10:12:10 GMT

This should do it for you

Hope it helps

Re: Cumulative Calculation By each Row

Tue, 17 Mar 2015 12:24:42 GMT

Hi There

If I will hide the expression then Column(2) - Column(4) expression will not work.

Without using the column name how can we calculate the cumulative sum.

Re: Cumulative Calculation By each Row

Wed, 18 Mar 2015 08:57:03 GMT

Greetings

The QV model is not attaching here for some reason.

I would like to show only the result.

Please advise.

Re: Cumulative Calculation By each Row

Roop — Wed, 18 Mar 2015 09:58:42 GMT

Use the rules added together :

if(Dimensionality()>0, if(aggr(min(period),year)>0, sum(exp1), sum(exp1) + rangesum(Before(Total Exp1YTD))), Sum(TOTAL exp1))

if(Dimensionality()>0, if(aggr(min(period),year)>0, sum(exp2), sum(exp2) + rangesum(Before(Total Exp2YTD))), Sum(TOTAL exp2))

With this you don't need any intervening calculations

So you only need the total line and not Exp1YTD and Exp2YTD

Hope this helps

Re: Cumulative Calculation By each Row

Wed, 18 Mar 2015 10:03:08 GMT

Hi Rupert

Thanks for the response.

In the calculation Exp1YTD and Exp2YTD have used which are the name of the expression.

May you please provide me the sample qv model.

Many Tx!

Re: Cumulative Calculation By each Row

Roop — Wed, 18 Mar 2015 10:05:55 GMT

Sorry I think that was slightly off ....

Here is the new result and the QV Model

Re: Cumulative Calculation By each Row

Wed, 18 Mar 2015 10:43:02 GMT

Hi Rupert

The calculation does not seem right.

for period 1 : Result = 100

2: 100+200-0 =300

3: 300+40 -25 = 315

4: 315+40 -25 = 330

Re: Cumulative Calculation By each Row

Roop — Wed, 18 Mar 2015 11:01:04 GMT

The results are the same now except that I have made Exp2 in period 1 set to zero as it does not figure in the calculation.

Re: Cumulative Calculation By each Row

Wed, 18 Mar 2015 11:24:31 GMT

Hi Rupert

I have checked the attached qv it showing the same result.

It is showing:

It must show

Please assist!!

Re: Cumulative Calculation By each Row

Roop — Wed, 18 Mar 2015 11:26:34 GMT

apologies - I did not include the correct application .....

Re: Cumulative Calculation By each Row

Wed, 18 Mar 2015 11:39:17 GMT

Hi Rupert

Appreciate your effort.

I can see the Exp1 and Exp2 is not providing the origianl values but the cumulative values.

I would like to see the below:

Please advise if it is possible.

Re: Cumulative Calculation By each Row

Roop — Wed, 18 Mar 2015 11:46:05 GMT

Sorted ....

I was showing the YTD values for Exp1 and Exp2. I have left them in the chart as commented out so you can revert to using them if you wish later on.

Re: Cumulative Calculation By each Row

Wed, 18 Mar 2015 11:53:57 GMT

Hi Rupert

Thanks for your reply!

Exactly the same I was getting.

But the result is not right. Please check the highlighted numbers.

It must show :

If we performing the cumulative calculation on same row then it is not giving correct result.

Please help.

Re: Cumulative Calculation By each Row

Roop — Wed, 18 Mar 2015 12:12:12 GMT

Re: Cumulative Calculation By each Row

Wed, 18 Mar 2015 12:42:41 GMT

Thanks a Million Rupert.

Much appreciated!