topic Re: Accumulative Sums - Pivot multiple dimensions in QlikView

Accumulative Sums - Pivot multiple dimensions

allan_vittrup — Sat, 16 Nov 2024 01:35:56 GMT

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.

Re: Accumulative Sums - Pivot multiple dimensions

Anil_Babu_Samineni — Fri, 03 Jan 2020 13:44:50 GMT

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

Re: Accumulative Sums - Pivot multiple dimensions

allan_vittrup — Fri, 03 Jan 2020 13:47:40 GMT

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.

Re: Accumulative Sums - Pivot multiple dimensions

Kushal_Chawda — Fri, 03 Jan 2020 14:02:26 GMT

try below

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

Re: Accumulative Sums - Pivot multiple dimensions

allan_vittrup — Fri, 03 Jan 2020 14:20:14 GMT

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:

Re: Accumulative Sums - Pivot multiple dimensions

Kushal_Chawda — Fri, 03 Jan 2020 14:52:12 GMT

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;

Re: Accumulative Sums - Pivot multiple dimensions

allan_vittrup — Mon, 06 Jan 2020 08:52:09 GMT

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.

Re: Accumulative Sums - Pivot multiple dimensions

qliksus — Tue, 07 Jan 2020 04:20:11 GMT

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