Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
allan_vittrup
Partner - Contributor III
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:

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.

Labels (3)
1 Solution

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

 

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 - Contributor III
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.

I have added the file.

Kushal_Chawda

try below

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

allan_vittrup
Partner - Contributor III
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:

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 - Contributor III
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.

 

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