Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

ecabanas
Contributor

Total bad calculation in pivot table

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

table.jpg

1 Solution

Accepted Solutions
whiteline
Honored Contributor II

Re: Total bad calculation in pivot table

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)

2 Replies
whiteline
Honored Contributor II

Re: Total bad calculation in pivot table

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)

ecabanas
Contributor

Re: Total bad calculation in pivot table

Hi Whiteline,

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