Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a pivot table with calculated dimensions and two expressions that are in the crosstab part of the table.
Customer | Quote Number | Quote Own Org | Quote Fulfill Org | Quote Line No | Quoted Price | Total Quoted | Invoiced Qty | Total Billed | Remaining | May-2015 Dispatched | Total Revenue |
Customer Name | 34206 | EU | EU | 16.1 | 1 | 1 | 1 | 1 | 0 | - | - |
Customer Name | 34206 | EU | EU | 14.1 | 1 | 1 | 1 | 0 | 1 | - | - |
Customer Name | 34206 | EU | EU | 12.1 | 1 | 1 | 1 | 0 | 1 | 1.00 | 1.00 |
Customer Name | 34206 | EU | EU | 3.1 | 1.00 | 1 | 1 | 1 | 0 | 1.00 | 1.00 |
Customer Name | 34206 | EU | EU | 2.1 | 1.00 | 4 | 4 | 2 | 2 | 196.63 | 137.50 |
Quote Total | ?????? | ?????? | ?????? | ?????? | 200.65 | 140.31 |
How do i get subtotals appearing for the "Total Quoted", "Invoiced Qty", "Total Billed","Remaining" which are calculated dimensions columns?
The "Dispatched" and "Total Revenue" columns are expressions and appear for each month of the year and the sub-total works properly for each month.
I dont think that the dimensions are the issue - rather the expressions. You may have expressions using naked fields*, and these cannot be evaluated at a total level as there are more than one possible value for the field. If you post more detail, at least the full set of dimensions and expressions, how you want to break for subtotals and the complete definition of any variables you use in any of the above, you will be able to get useful and accurate suggestions.
* fields outside of an aggregate function (like Sum(), Count(), Max()...)
Hi Jonathan the expressions totals in my applications are correct; I just made a mistake when entering them for this question and didn't realize.
The columns not mention below are all normal dimensions with just a [field name] selected; this also includes the "Quoted_Month_Year" column which is used in the pivot part of the table with the Dispatched and Total Revenue columns.
DIMENSIONS.
----------------
Calculated Dimension Column - Quoted Price
=NUM(AGGR(
SUM($(vOrderPrice)),
%ORDER_KEY
),'$(MoneyFormat)')
Calculated Dimension Column - Ordered
=NUM(AGGR(
SUM($(vOrderQuantity)),
%ORDER_KEY
),'$(MoneyFormat)')
Calculated Dimension Column - Total Quoted
=NUM(AGGR(
SUM($(vHistoricQuotedMonthly)),
%ORDER_KEY
),'$(MoneyFormat)')
Calculated Dimension Column - Invoiced Qty.
=NUM(AGGR(
SUM(if([Invoice Type]<>'UNB',Alt([Quantity Invoiced],0),0)),
%ORDER_KEY
),'$(MoneyFormat)')
Calculated Dimension Column - Total Billed.
=NUM(AGGR(
SUM($(vRevenueBilledAmt)),
%ORDER_KEY
),'$(MoneyFormat)')
Calculated Dimension Column - Remaining Column.
=NUM(AGGR(SUM($(vHistoricQuotedMonthly))-sum($(vRevenue)),%ORDER_KEY),'$(MoneyFormat)')
Calculated Dimension Column - % Remaining
=NUM(alt(AGGR(if((SUM($(vHistoricQuotedMonthly))-sum($(vRevenue)))/(SUM($(vHistoricQuotedMonthly)))=-0,0,((SUM($(vHistoricQuotedMonthly))-sum($(vRevenue)))/(SUM($(vHistoricQuotedMonthly))))),[Quote Number],[Quote Line Number]),0),'#,##0.00%')
EXPRESSIONS.
----------------
Expression - Dispatched.
=sum(AGGR(SUM(Alt([Quantity Invoiced],0)),%ORDER_KEY))
Expression - Total Revenue.
=NUM(sum(AGGR(SUM($(vRevenue)),
%ORDER_KEY
)),'$(MoneyFormat)')
Thanks
Kevin.