
Re: Pivot not allowing Subtotals
John Witherspoon Oct 31, 2012 7:27 PM (in response to Eric Schmid)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))

Re: Pivot not allowing Subtotals
Eric Schmid Nov 1, 2012 12:02 AM (in response to John Witherspoon )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.

test2.qvw 168.8 K

Re: Pivot not allowing Subtotals
Jonathan Dienst Nov 1, 2012 2:11 AM (in response to Eric Schmid)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

Re: Pivot not allowing Subtotals
Eric Schmid Nov 1, 2012 9:08 AM (in response to Jonathan Dienst )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]))


