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: 
Not applicable

Cumulate subtotals over dimensions in pivot table

Hi,

I can cumulate amount in one dimentsion, but now I would like to cumulate subtotals over dimensions in pivot table.

Example:

LOAD * INLINE [
Dept, SalesRep, ProductName, Amount
Dept1, Emp1, Product1, 200
Dept1, Emp1, Product2, 600
Dept1, Emp1, Product3, 100
Dept1, Emp2, Product2, 300
Dept1, Emp2, Product3, 900
Dept2, Emp3, Product1, 150
Dept2, Emp3, Product3, 450
Dept2, Emp4, Product1, 350
Dept2, Emp4, Product2, 300
Dept2, Emp4, Product3, 650
];

if dimension is Dept, SalesRep, ProductName, I can get subtotal of  Dept, SalesRep and ProductName.

And I set cumulative of Amount, but it only show cumulative of dimension Dept, SalesRep and ProductName.

I want to get cumulative of Dept, SalesRep.

How can I do?

I have upload the example as attachement.

Thanks!

1 Reply
stephencredmond
Luminary Alumni
Luminary Alumni

Hi,

Are you looking for something like:

   RangeSum(above(total Sum(Amount), 0, RowNo(TOTAL)))

Within a pivot table, these functions can work unexpectedly (but logically!).  Use of TOTAL can help here.

Regards,


Stephen