Incorrect total in pivot table.

[#1]

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!

[jerem1234]

Try something like:

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

Hope this helps!

[#3]

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!

[simenkg]

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

[jerem1234]

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!

[giakoum]

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

[#7]

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!

[simenkg]

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

[simenkg]

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

[#10]

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!