Cumulative Calculation By each Row

[#1]

Greetings,

I would like to achieve cumulative calculation using pivot table.

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

[MK_QSL]

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

[Roop]

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.

[#4]

Thanks for replying.

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

[Roop]

This should do it for you

Hope it helps

[#6]

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.

[#7]

Greetings

The QV model is not attaching here for some reason.

I would like to show only the result.

Please advise.

[Roop]

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

[#9]

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!

[Roop]

Sorry I think that was slightly off ....

Here is the new result and the QV Model