Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
newuser
Creator II
Creator II

RangeSum in Pivot Table Multiple Dimensions Returns Incorrect Results When Expanded

Hi. I have a pivot table with two dimensions, and I'm using RangeSum with Above to generate a cumulative total. The following expression works when the pivot table is collapsed; however, when I expand it, the cumulative total returns zero. Any ideas how to fix? I saw a similar post except it had multiple columns across here --> 

https://community.qlik.com/t5/Qlik-Sense-App-Development/Cumulative-Sum-with-Two-Dimensions-in-a-Piv...

 

However, the above post did not seem to fix my issue even when I tried to adapt it. Any tips appreciated.

 

Expression below works if pivot table is collapsed (returns zero if expanded):

rangesum(above(count(distinct {<Year_Paid_Adj=, Month_Paid_Adj=, Paid_Adj_YrMo=, ActivityDescr= >}
if(isnull(closed_date_use), acctpol)),
0, RowNo()))

Labels (4)
8 Replies
Kushal_Chawda

@newuser  what are the dimensions of the pivot?

newuser
Creator II
Creator II
Author

MonthYear and Vendor (when I expand into multiple Vendors under a given MonthYear, that is what the incorrect result occurs) - the MonthYear cumulative total changes to zero, and the subtotals by vendor are cumulative within a given MonthYear but not cumulative by vendor (the latter is what I need). If that makes any sense.

newuser
Creator II
Creator II
Author

 

Just to illustrate the issue a bit further, below is the expected result when expanding on MonthYear in the pivot table (fake data below). I cannot get the cumulative totals under Vendor to appear correctly. The cumulative totals in bold below appear correctly by MonthYear, just not by Vendor. Any ideas?

MonthYear

Vendor

Count acctpol

Cumulative

1/1/2019

 

30

30

 

Dog

10

10

 

Cat

15

15

 

Bird

5

5

2/1/2019

 

27

57

 

Dog

12

22

 

Cat

8

23

 

Bird

7

12

 

sunny_talwar

@newuser May be try this

 

Sum({<Year_Paid_Adj, Month_Paid_Adj, Paid_Adj_YrMo, ActivityDescr>} Aggr(

RangeSum(Above(Count(DISTINCY {<Year_Paid_Adj, Month_Paid_Adj, Paid_Adj_YrMo, ActivityDescr>} If(IsNull(closed_date_use), acctpol)),
0, RowNo()))

, Vendor, MonthYear))

 

newuser
Creator II
Creator II
Author

Totals are larger than expected ... 😞

newuser
Creator II
Creator II
Author

I think I got it to work, but I ran into a different issue to make this work. Suppose I have the following set analysis where I only want the set qualifiers to apply to the first Placed element - any way to achieve? I tried breaking this up into two sum()'s, but then embedding that within an "Above" and RangeSum didn't seem to work in Qlik.

sum({<Year_Paid_Adj=, Month_Paid_Adj=, Paid_Adj_YrMo=>}
if(closedflag = 0, Placed - Payment))

Kushal_Chawda

can you share sample qvf file with expected output?

sunny_talwar

@newuser No idea what you mean. May be a sample might be helpful to see your issue