Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
lucas_zec
Contributor II
Contributor II

"Aggr" function with "IF" & "AND/OR" conditions

Hi there!

I have been struggling a lot and could't find something related in the messages.

As an example:

Table AND.png

So I'm trying to perform an analysis similar to the example above: with purchases with items of the fruits category AND alfa brand.

As you can see, there are fruits that do not belong to this brand, there are items belonging to this brand that are not fruits and among fruits that are from this brand, there are several different items.

So far, I've tried something along the lines of:

count(distinct (if(aggr(NODISTINCT count({<CATEGORY = {'Fruits'}>} CATEGORY),PURCHASE)>0,PURCHASE)))

and this has worked! However, when I tried to add the brand field

count(if((aggr(count({<CATEGORY = {'Fruits}>} CATEGORY),PURCHASE)>0)

and (aggr(count({<BRAND = {'Alfa'}>} BRAND),PURCHASE)>0),PURCHASE))


it doesn't work anymore.


BTW, by not working I don't mean syntax (syntax does work), but I don't get the desired result. For the example above, I would expect to obtain 3 purchases and I'm getting let's say 10. Same goes when trying to sum the volume and revenue.


Can anyone please help me?


Thanks a lot!


14 Replies
sunny_talwar

This looks right to you?

Capture.PNG

Count(DISTINCT {<Purchase = e({<Category = {'Fruits'}, Brand = {'Alfa'}>})>} Purchase)

Sum({<Purchase = e({<Category = {'Fruits'}, Brand = {'Alfa'}>})>} Volume)

Sum({<Purchase = e({<Category = {'Fruits'}, Brand = {'Alfa'}>})>} Revenue)

lucas_zec
Contributor II
Contributor II
Author

I'll test it soon (not in the office right now so I can't access the database), but this looks very promissing, so thanks in advance!

One question though, you didn't use the aggr function and yet it seems to yields the same result.

Do you have a reason not to use the aggr on this particular case?

And if so, do you know the main difference between them?

Thanks a lot again!

sunny_talwar

Well aggr() is usually my last resort before I have tried other ways to solve my problem. In this case it was a perfect case of using p() and e() which is why I gave you the option to use set analysis instead of Aggr(). I think this might be solvable using Aggr(), but I won't even try it because you will be drastically slowing down your app, in addition to wasting a lot of time by trying to solve this using Aggr().

Best,

Sunny

lucas_zec
Contributor II
Contributor II
Author

Thanks a lot!

It has worked!

If you wanted to use OR, how would you do it?

For example, purchases containing fruits from brands A or B.

In excel I would try something like:

=if(and(category="fruits";or(brand="A";brand="B")))

Thanks again!

sunny_talwar

I am not entirely sure... but may be try one of these

Sum({<Purchase = p({<Category = {'Fruits'}, Brand = {'Alfa', 'Beta'}>})>} Revenue)

or

Sum({<Purchase = p({<Category = {'Fruits'}, Brand = {'Alfa'}>})+p({<Category = {'Fruits'}, Brand = {'Beta'}>})>} Revenue)