Accumulative Sums - Pivot multiple dimensions

[allan_vittrup]

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.

[Anil_Babu_Samineni]

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

[allan_vittrup]

Hi Anil.

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

I have added the file.

[Kushal_Chawda]

try below

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

[allan_vittrup]

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:

[Kushal_Chawda]

It will be better create it in script

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

Final:
Load *,
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;

[allan_vittrup · Accepted solution]

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.

[qliksus]

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