Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Incorrect total in pivot table.

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])

PRINT.jpg

(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!

10 Replies
jerem1234
Specialist II
Specialist II

Try something like:

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

Hope this helps!

Not applicable
Author

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
Partner
Partner

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

jerem1234
Specialist II
Specialist II

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
Partner
Partner

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

Not applicable
Author

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
Partner
Partner

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

simenkg
Partner
Partner


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

Not applicable
Author

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

PRINT.jpg

Thanks again!