Calculated dimension based on multiple conditions (Aggr() vs SetAnalysis)

[#1]

Best QlikView Community members,

I’m trying to create a calculated dimension based om multiple conditions (The equivalent to Sumifs() in Excel). Well, I began on my actual dataset using first the Aggr() function in the following expression: if(AccountNr='106K',sum(Outcome)-aggr(sum(if (AccountNr='4941', Outcome,0)), Name, MonthYear), sum(Outcome))

where I’m trying to subtract the outcome for the account number 4941 from that for the account number 106K for the corresponding person and date. Obviously it’s not working. And if you’re wondering why I’m using this particular expression is that because it’s working perfectly in a similar but simpler dataset that I created as an experimental example where I’m using the following expression: if(AccountType='A',sum(Balance)-aggr(sum(if (AccountType='SubA', Balance,0)), Name,ID,Date), sum(Balance)) In this case I’m subtracting the balance for the AccountTypeID (SubA) from that for the AccountTypeID (A) for the corresponding name and date and it’s working effortlessly. What’s puzzling me is that the same expression is working in one case but not in another similar one.  I wonder if I got it right experimentally by doing wrong?! And using SetAnalysis in different ways doesn’t seem to help either no matter how much I try to rewrite the syntax with all the strange signs that’s required.  Anyway, I’m attaching the 2 examples hoping that someone can help me find what I’m doing wrong before I run out of coffee and patience.

Best regards to you all.

[Gysbert_Wassenaar · Accepted solution]

Try:

if(AccountNr='106K',sum(Outcome)-sum(aggr(NODISTINCT sum({<AccountNr={'4941'}>} Outcome), Name, MonthYear)), sum(Outcome))

see attached qvw

[#3]

Hi there Gysbert and thanks a lot for your answer.

Of Corse I just added the NODISTINCT and it worked like a dream. You've seen maybe that I tried almost everything but the magic of "NODISTINCT". Thanks a lot once again for this perfect answer, I'm very grateful.