topic Re: Problem with AGGR function in QlikView

Problem with AGGR function

doespirito — Fri, 04 Aug 2017 10:22:08 GMT

hi ,

I have a problem with aggr function.

(see attached qvw.)

I have two tables :

- one named PAIE with AMOUNT per COST_C/YEAR/MONTH/PERSO_ID/ACCOUNT

- one named TAX with with TAXE_RATE per YEAR/MONTH/PERSO_ID

I have linked them in qlikview with a key (YEAR/MONTH/PERSO_ID) so every line of PAIE table has the good tax rate from TAX table.

Know, i want to make a pivot table aggregated on COST_C for only ACCOUNT=32007.

In excel, the result would be :

witch is made like this :

I have tred to do that with set analysis and aggr function but it gives me a wrong result.

Could you help me ?

Thank you very much

Arnault

zhadrakas — Fri, 04 Aug 2017 10:34:33 GMT

try this Expression

sum(AMOUNT) + sum(AMOUNT*TAX_RATE)

or See attended qvw

regards

tim

ahaahaaha — Fri, 04 Aug 2017 10:40:43 GMT

Hi,

May be like this?

Sum ({<ACCOUNT={'32007'}>}TAX_RATE)

Result

Example at attached file

Regards,

Andrey

doespirito — Fri, 04 Aug 2017 10:58:05 GMT

thank you Andrey and Tim,

but i only want to see cost center in the pivot table dimension and the calculation for only three lines witch gives 225.

sunny_talwar — Fri, 04 Aug 2017 11:20:43 GMT

Remove other dimensions and Tim's solution seems to be working the way you would want