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

Pivot not allowing Subtotals

Hello,

Please see the attached example. I am trying to subtotal the Opportunity values by each Supplier_Proposal. I have selected the Partial Sums check box and Expand all options as other discussions have suggested without success. Can total Opportunity be isolated by Supplier_Proposal in this Pivot or a Bar chart? A straight table allows summation by all Suppliers_Proposal, but this does not do me any good. Any suggestions are welcome.

Thanks!

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

Well, I'm not certain if you just want a straight sum, but if so, if you change your first two expressions to these, you can subtotal the rows:

sum(aggr(Rate_Proposal*[Days Proposed],Supplier_Proposal,[Normalized Role]))
sum(aggr(Avg(Rate)*[Days Proposed],Supplier_Proposal,[Normalized Role]))

To explain, pivot tables reevaluate the expression for the subtotals rather than summing up the rows.  To get a sum of rows, the general form is this:

sum(aggr(your expression, your dimensions))

View solution in original post

4 Replies
johnw
Champion III
Champion III

Well, I'm not certain if you just want a straight sum, but if so, if you change your first two expressions to these, you can subtotal the rows:

sum(aggr(Rate_Proposal*[Days Proposed],Supplier_Proposal,[Normalized Role]))
sum(aggr(Avg(Rate)*[Days Proposed],Supplier_Proposal,[Normalized Role]))

To explain, pivot tables reevaluate the expression for the subtotals rather than summing up the rows.  To get a sum of rows, the general form is this:

sum(aggr(your expression, your dimensions))

Not applicable
Author

Thanks John, the above got me part of the way there in that I can now calculate the subtotal of each supplier as shown in the updated example attached.

My next issue is that the the subtotals do not recognize my IF modifier that zero's out negative values. The reason I need this function to work is that negative numbers do not present an 'Opportunity' and therefore should not be calculated against the subtotal/total Opportunity for that Supplier.

For example, the Opportunity values for Supplier_Proposal 'T' are -8, 27, 18 which subtotal correctly to 37. If I zero out -8 with an IF statement, however, the total remains 37 instead of updating to the sum total of 0, 27, 18. The new value should then be 45, but it remains at 37.

I performed the same IF on the BM expression to zero out that value for Supplier_Proposal 'T' to test and the same thing occurs where the sum of 0, 45, and 20 is incorrectly shown to be 77 rather than 65.

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

Try this for your 'Opportunity' expression:

Sum(Aggr(

RangeMax(0,

          sum(aggr(Rate_Proposal*[Days Proposed],Supplier_Proposal,[Normalized Role]))

          -

          sum(aggr(Avg(Rate)*[Days Proposed],Supplier_Proposal,[Normalized Role]))

), Supplier_Proposal,[Normalized Role]))

I get 0, 27, 18 and subtotal 45 for proposal T

Hope that helps

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

That works Jonathan, thank you! Any idea why it doesn't recognize the short version using the names of the previous expressions?:

Sum(Aggr(

RangeMax(0,

          Proposed

          -

          BM

), Supplier_Proposal,[Normalized Role]))