Accumulated Value in pivot with n dimensions, where value is reset when dim 1 changes

[allan_vittrup]

Hi.

In a pivot table I need to Acc. a QTY per Item over date and Trans Type.

Hovever, I have several additional dimensions that are conditional and can be selected to be shown/hidden. An Aggr with all dimensions is therefore not an option!

There are three trans types:

Stock, Sales Order and Purchase. Stock always has to be first value.

At first I tried:

RangeSum(Above(TOTAL Sum(QTY), 0, RowNo(TOTAL)))

The problem was of course that the value was just accumulated disregarding the item.

The value needs to be reset when the Item changes and then start the accumulation from the "Stock" value.

I tried to do an if that would determine if we had a new item and therefore my final guess was this:

If(Above(TOTAL Item) = Item,
RangeSum(Above(TOTAL Sum(QTY), 0, RowNo(TOTAL))),
Sum(QTY)
)

The result is that the first line for the new item works but then the accumulation begins from the top. How do I force the calculation with the Item?

[Anil_Babu_Samineni]

Try this

If(Item=Above(TOTAL Item), RangeSum(Above(TOTAL Sum(QTY), 0, RowNo(TOTAL))), Sum(QTY))

[allan_vittrup]

Hi Anil.

Thank you for your reply.  Your solution seems to work within the "Trans Type" only. Whenever we get to a new date we start from the first value:

[Anil_Babu_Samineni]

Try to use Another condition for Date field also, If that is the case?

If(Item=Above(TOTAL Item) or Date=Above(TOTAL Date), RangeSum(Above(Sum(QTY), 0, RowNo(TOTAL))), Sum(QTY))

[allan_vittrup]

Hi Anil.

The result is unfortunately the same. No values changed compared to the last solution.

[Anil_Babu_Samineni]

Can you please post some rows and provide the expected result, May be try with

If(Item=Above(TOTAL Item), If(Date=Above(TOTAL Date), RangeSum(Above(Sum(QTY), 0, RowNo(TOTAL))), Sum(QTY)))

[allan_vittrup]

Hi again Anil.

Thank you for another answer. Sorry, but now I get this result:

[allan_vittrup]

Let me try again:

Your alternative solutionThe solution I am looking for

[allan_vittrup · Accepted solution]

I ended up solving it quite simply this way in the chart expression:

if(Item<>Above(TOTAL Item),
Sum(QTY),
Sum(QTY) + Above(TOTAL [Qty Acc.])) // Where [Qty Acc.] is the header name.