topic Re: If - Sum - Aggr Formula in QlikView

If - Sum - Aggr Formula

christiana — Mon, 29 Oct 2018 10:29:16 GMT

Dear all,

we have been loading the accounts from the balance sheet into Qlikview and I want to show some accounts depending on the +/- side either on the active or liability side. The trigger is sometimes the positive or negative total of some accounts.

The first example is working fine - if the amount by company, Year, Period, Account is less than 0, then it should go to Accountgroup 2211000 if it is positive, then to 1208000:

For the accounts 160106 and 170100, the total of both is giving the direction (reclass group 1) - if it is less than 0 to 2160000 and if it is positive than to 1220000.

With the formula I've applied:

Qlikview uses the total of both to decide which account group to use, however this is only displayed in the line of one account (170100), but not on the level of 160106 - this one gets the 'xxx'.

What do I need to change in the formula, so that the account 160106 gets as well the AccountGroup attribute?

Many thansk to you!

Re: If - Sum - Aggr Formula

sunny_talwar — Mon, 29 Oct 2018 12:35:31 GMT

Not entirely sure I understand what you are after, would you be able to let us know what the expected output needs to look like?

Re: If - Sum - Aggr Formula

christiana — Mon, 29 Oct 2018 12:52:00 GMT

Of course - I'm expecting this:

Re: If - Sum - Aggr Formula

sunny_talwar — Mon, 29 Oct 2018 12:54:19 GMT

Try this for your calculated dimension

=if(Account='161100' and Amount<0, '2211000',

if(Account='161100' and Amount>0, '1208000',

if(Account='154000' and Amount<0, '2211000',

if(Account='154000' and Amount>0, '1208000',

if(Reclass='1' and aggr(NODISTINCT sum(Amount), Reclass, %Key)>0, '1220000',

if(Reclass='1' and aggr(NODISTINCT sum(Amount), Reclass, %Key)<0, '2160000', 'xxx'))))))

Re: If - Sum - Aggr Formula

christiana — Mon, 29 Oct 2018 12:58:26 GMT

It's working!

Many many thanks to you!

Would you mind explaining me what the Nodistinct is doing? I never heard about it....

Re: If - Sum - Aggr Formula

sunny_talwar — Mon, 29 Oct 2018 13:12:58 GMT

Check it here

What NODISTINCT parameter does in AGGR function?

Re: If - Sum - Aggr Formula

christiana — Mon, 29 Oct 2018 13:53:32 GMT

Thank you!

Re: If - Sum - Aggr Formula

sunny_talwar — Mon, 29 Oct 2018 13:54:58 GMT

No problem at all