Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
allan_vittrup
Partner
Partner

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:

WhatIWant.png

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:

WhatINeed.png

 Any ideas?

Test app is attached.

1 Solution

Accepted Solutions
allan_vittrup
Partner
Partner
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.

 

View solution in original post

7 Replies
Anil_Babu_Samineni

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)
allan_vittrup
Partner
Partner
Author

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
Partner
Partner
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:

Kush_Solution.png

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
Partner
Partner
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.

 

View solution in original post

qliksus
Specialist II
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()