Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
allan_vittrup
Partner - Contributor III
Partner - Contributor III

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

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?

Accumulated_Sum.png

Labels (3)
1 Solution

Accepted Solutions
allan_vittrup
Partner - Contributor III
Partner - Contributor III
Author

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.

View solution in original post

8 Replies
Anil_Babu_Samineni

Try this

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
allan_vittrup
Partner - Contributor III
Partner - Contributor III
Author

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:

Accumulated_Sum_Anil.png

 

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
allan_vittrup
Partner - Contributor III
Partner - Contributor III
Author

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
allan_vittrup
Partner - Contributor III
Partner - Contributor III
Author

Hi again Anil.

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

allan_vittrup
Partner - Contributor III
Partner - Contributor III
Author

Let me try again:

Your alternative solutionYour alternative solutionThe solution I am looking forThe solution I am looking for

allan_vittrup
Partner - Contributor III
Partner - Contributor III
Author

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.