Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 -->
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()))
@newuser what are the dimensions of the pivot?
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.
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 |
@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))
Totals are larger than expected ... 😞
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))
can you share sample qvf file with expected output?
@newuser No idea what you mean. May be a sample might be helpful to see your issue