topic Re: Incorrect total in pivot table. in QlikView

Incorrect total in pivot table.

Mon, 04 Aug 2014 13:57:08 GMT

Hi, everyone - This is my problem:

I have three dimensions:

- CostCenter

- Year

- Month

And four expressions:

- ABM Total (SUM(AMOUNT))

- ABM Currency (ABM_CURRENCY): USD / PES / -

- ABM Currency Value (IF([ABM Currency] = 'USD', AVG(CURRENCY_VALUE), 1))

- TOTAL ([ABM Total] * [ABM Currency Value])

(this is just part of the pivot table, of course)

The total from the TOTAL column does not work OK. It is as if it's just taking ABM TOTAL total and multipliying for ABM CURRENCY VALUE total, which, of course, is not what I want. I want the sum of its rows, instead.

I have read about similar issues in this community and about AGGR function and I think the solution has to do with it, but I just can't manage to show the right total.

Could anybody lend me a hand? Thank you very much!

Re: Incorrect total in pivot table.

jerem1234 — Mon, 04 Aug 2014 14:02:02 GMT

Try something like:

sum(aggr(SUM(AMOUNT)*IF([ABM Currency] = 'USD', AVG(CURRENCY_VALUE), 1), CostCenter, Year, Month))

Hope this helps!

Re: Incorrect total in pivot table.

Mon, 04 Aug 2014 14:28:54 GMT

I tried exactly that, Jeremiah, but it did not work. Now I have the same values on both ABM TOTAL and TOTAL columns : (

Thanks anyway!

Re: Incorrect total in pivot table.

simenkg — Mon, 04 Aug 2014 14:30:40 GMT

sum(aggr(SUM(AMOUNT)*IF([ABM Currency] = 'USD', AVG(CURRENCY_VALUE), 1), CostCenter, Year, Month,[ABM Currency]))

Re: Incorrect total in pivot table.

jerem1234 — Mon, 04 Aug 2014 14:37:05 GMT

Try this:

sum(aggr(SUM(AMOUNT)*IF(ABM_CURRENCY = 'USD', AVG(CURRENCY_VALUE), 1), CostCenter, Year, Month))

ABM_CURRENCY should be the field, not the expression label of another expression.

Hope this helps!

Re: Incorrect total in pivot table.

giakoum — Mon, 04 Aug 2014 14:39:17 GMT

does it not work as expected when you use Sum of rows (Expression tab)?

If it is not available in a pivot, try changing it to straight table, check Sum of rows and change it back to pivot

Re: Incorrect total in pivot table.

Mon, 04 Aug 2014 14:40:08 GMT

That didn't work for me, Simen. I'm not even seeing TOTAL values now. I didn't know you coud use expressions in AGGR functions.

Thanks anyway!

Re: Incorrect total in pivot table.

simenkg — Mon, 04 Aug 2014 14:41:36 GMT

You cant but how do you calculate the currency? You should use the dimension in your AGGR.

Re: Incorrect total in pivot table.

simenkg — Mon, 04 Aug 2014 14:43:21 GMT

sum(aggr(SUM(AMOUNT)*IF(ABM_CURRENCY = 'USD', AVG(CURRENCY_VALUE), 1), CostCenter, Year, Month,ABM_CURRENCY))

Re: Incorrect total in pivot table.

Mon, 04 Aug 2014 14:50:55 GMT

Good news is that now the total is the sum of rows. Bad news is that TOTAL is now not equal to ABM TOTAL * ABM CURRENCY VALUE. (Please, disregard the following print's language).

Thanks again!

Re: Incorrect total in pivot table.

jerem1234 — Mon, 04 Aug 2014 15:07:28 GMT

That's because when you drill-down in your CostCenter dimension, you have some values with ABM_CURRENCY equal to 'USD'. For example:

In your previous post, CostCenter of 61, Year 2014, and Month of Feb, the ABM_CURRENCY is 'USD'. Therefore, the aggr would calculate this even when you are drilled up only at CostCenter. It will sum up the values within the dimension.

So when you are looking at it drilled up to only CostCenter, it is calculating (from your previous post) 0+69.500+0+0+423.686,85+157.050,79+....

Note this will not be the same value of TOTAL ABM when you are drilled up to only CostCenter. How do you know which values have 'USD' for ABM_CURRENCY and which don't?

How exactly do you want to calculate? Keep in mind when you are drilling down or when you are drilled up.

Hope this helps!