# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
An issue has been identified on Qlik Cloud hub, please visit our Status Update Page for details: GET THE LATEST
cancel
Showing results for
Did you mean:
Partner - Contributor III

## Accumulative Sums - Pivot multiple dimensions

Hi.

I have a pivot table where I have to do an accumulative sum per Dim 1 (Item) By Date and TransType.

I am using Qlikview 12, April 2019.

What I have now using this expression,

aggr(RangeSum(Above(TOTAL Sum(QTY),0,RowNo(TOTAL))),
(Item,(TEXT,ASCENDING)),
(Date,(NUMERIC,ASCENDING)),
(TransTypeSorting,(NUMERIC,ASCENDING)))

is this:

The problem is that the accumulation continues when a new Item is met. I need the calculation to restart when a new item is met.

What I need is this:

Any ideas?

Test app is attached.

Labels (3)

• ### Rangesum() Function

1 Solution

Accepted Solutions
Partner - Contributor III
Author

Hi Kush.

Thank you for your reply. Your solution will work but I will then be dependent on correct sorting from data in load script and sorting in the chart. To avoid that I would like to solve it in the chart expression.

I ended up solving it 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.

7 Replies

We don't know why second thread opened by you Original Anyway, Can you please attach excel file also "Data.xlsx"?

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)
Partner - Contributor III
Author

Hi Anil.

I opened a new simplified thread with an app as attachment. If this is wrong I will close the original one.

try below

=if(Item<>Above(total Item),
Sum([QTY Col]),YourCummulativeExpression)

Partner - Contributor III
Author

Hi Kush.

I changed the expression to this:

if(Item<>Above(TOTAL Item),
Sum(QTY),
aggr(RangeSum(Above(TOTAL Sum(QTY),0,RowNo(TOTAL))),
(Item,(TEXT,ASCENDING)),
(Date,(NUMERIC,ASCENDING)),
(TransTypeSorting,(NUMERIC,ASCENDING))))

I have actually come across that solution and it works for the specific line where the item changes. but on the next line it accumulates from the top once again:

It will be better create it in script

Data:
Item,
"Date",
"Trans Type",
"QTY Col"
FROM [lib://Test/Data/Files/Data.xlsx]
(ooxml, embedded labels, table is Ark1);

Final:
if(RowNo()=1 or Item<>Previous(Item),"QTY Col", RangeSum(Peek(AccumQty),"QTY Col")) as AccumQty
Resident Data
Order by Item,Date,"Trans Type";

Drop Table Data;

Partner - Contributor III
Author

Hi Kush.

Thank you for your reply. Your solution will work but I will then be dependent on correct sorting from data in load script and sorting in the chart. To avoid that I would like to solve it in the chart expression.

I ended up solving it 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.

Specialist II

Rowno(total) give numbers to whole table based on the last dimension but Rowno() reset the number for each dimension value  . Something like the below should give you what you need provide the sorting done properly  in the table

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

Community Browser