topic Re: Total bad calculation in pivot table in QlikView

Total bad calculation in pivot table

ecabanas — Mon, 29 Oct 2012 15:02:47 GMT

Hi,

I'm using a pivot table with the units, sales and Standard cost...I did an expression to know the margin in %...every article is well calculated, but the subtotal did not work, could anybody help me?

Thank's

Sum(Aggr(((Sum(EVE_AMOUNTMSTEXCLTAX)+Sum (QTYORDERED)*Avg (COSTEUDCASTD))/Sum(EVE_AMOUNTMSTEXCLTAX)),FAMILIA,ITEMID))

Familia and item are the Dimensions

Re: Total bad calculation in pivot table

whiteline — Mon, 29 Oct 2012 19:34:11 GMT

Hi.

The expression gives you the sum of margins:

Sum(Aggr(((Sum(EVE_AMOUNTMSTEXCLTAX)+Sum (QTYORDERED)*Avg (COSTEUDCASTD))/Sum(EVE_AMOUNTMSTEXCLTAX)),FAMILIA,ITEMID))

I think you should first get Sum() for each FAMILIA,ITEMID and then divide it on Sum(EVE_AMOUNTMSTEXCLTAX):

=Sum(Aggr(Sum(EVE_AMOUNTMSTEXCLTAX)+Sum (QTYORDERED)*Avg (COSTEUDCASTD),FAMILIA,ITEMID)) / Sum(EVE_AMOUNTMSTEXCLTAX)

Re: Total bad calculation in pivot table

ecabanas — Tue, 30 Oct 2012 11:47:38 GMT

Hi Whiteline,

Thank you very much!!!!! EXCELENT!!!