topic Calculated Dimensions with subtotals in QlikView

Calculated Dimensions with subtotals

Tue, 22 Sep 2015 12:01:56 GMT

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.

Re: Calculated Dimensions with subtotals

jonathandienst — Tue, 22 Sep 2015 13:39:35 GMT

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()...)

Re: Calculated Dimensions with subtotals

Tue, 22 Sep 2015 14:31:18 GMT

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.