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

Mon, 07 Jul 2014 13:39:04 GMT

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.

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

Gysbert_Wassenaar — Mon, 07 Jul 2014 14:49:10 GMT

Try:

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

see attached qvw

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

Mon, 07 Jul 2014 17:11:32 GMT

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.

topic Re: Calculated dimension based on multiple conditions (Aggr() vs SetAnalysis) in QlikView

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

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

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