topic Re: Aggr function is not working correctly in QlikView

Aggr function is not working correctly

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Dear QV experts,

As you can see in the pivot table below I have a problem with the aggr function. In the 7th row (Tot Geinv vermogen saldo) I would like to show the total across all Vestiging_Naam for each AsOfPeriod using the following expression:

=aggr(Sum({<Resultaatgroep1={[Geinv]},PeriodType={[Current]}>} BedragSaldi),AsOfPeriod)

However this expression is only shown correctly on the first line and is shown incorrectly (in fact not all) in the subsequent lines.

Do you have any thoughts what I am doing wrong.

Regards,

Rudi

Re: Aggr function is not working correctly

Kushal_Chawda — Wed, 30 Mar 2016 14:56:08 GMT

try this

sum(aggr(Sum({<Resultaatgroep1={[Geinv]},PeriodType={[Current]}>} BedragSaldi),Vestiging_Naam,AsOfPeriod))

Re: Aggr function is not working correctly

sunny_talwar — Wed, 30 Mar 2016 14:58:59 GMT

Like Kush pointed out, you need to add all your dimensions within the Aggr function. What all dimensions do you have?

Re: Aggr function is not working correctly

Anonymous — Wed, 30 Mar 2016 15:11:52 GMT

Kush, Sunny,

First of all thanks for your quick responses.

I have two dimensions: AsOfPeriod (the time dimension) and Vestiging_Naam (in English: the outlet). I need in each line the total of all outlets (Vestiging_Naam) per period (as reference number). The suggested expression of Kush is giving me - as you can see below - the total per outlet but not the total of all outlets.

Any thoughts here?

Many thx,

Rudi

Re: Aggr function is not working correctly

sunny_talwar — Wed, 30 Mar 2016 15:14:49 GMT

Can you try this:

Sum(TOTAL <AsOfPeriod> Aggr(Sum({<Resultaatgroep1={[Geinv]},PeriodType={[Current]}>}BedragSaldi),Vestiging_Naam,AsOfPeriod))

Re: Aggr function is not working correctly

Anonymous — Wed, 30 Mar 2016 15:18:29 GMT

Sunny,

This works!!!!

I now see what I've done wrong!

Many thx for your help!

Regards,

Rudi

Re: Aggr function is not working correctly

sunny_talwar — Wed, 30 Mar 2016 15:32:52 GMT

Not a problem. I am glad me and Kush were able to help you out