Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

lucas_zec
New Contributor

"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
MVP
MVP

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

Have you tried this may be

Count

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

Volume

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


Revenue

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


Capture.PNG

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

May be try this?

If(count({<CATEGORY = {'Fruits'}>} CATEGORY)>0 and

count({<BRAND = {'Alfa'}>} CATEGORY)>0, Count(PURCHASE))

Or

Sum(Aggr(If(count({<CATEGORY = {'Fruits'}>} CATEGORY)>0 and

count({<BRAND = {'Alfa'}>} CATEGORY)>0, Count(PURCHASE)), PURCHASE))

lucas_zec
New Contributor

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

Thanks!

Could you please clarify what does the "p" mean?

This approach seems to work for this particular case (which has surely helped me!) but it is not ideal since I can't seem to gather the "$-1" case properly, since if a purchase contains an item that isn't a fruit, it is counted if it does have an item that is a fruit. In the example above, $-1 <Fruit> should return 3 purchases (B, C and E) and it doesnt.

I believe that with the aggr this would be resolved, but I still can't work it with the "and" conector.

What do you think?

Thanks again!

lucas_zec
New Contributor

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

Thank you for your answer!

I'm not able to get the second part right though. The if inside de aggr doesn't seem to work.

Did you manage to do it with this exact syntax?

Thanks again!

MVP
MVP

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

You can read about p() and e() here

P() &amp; E() and where do you use them?

In the example above, $-1 <Fruit> should return 3 purchases (B, C and E)

How? What exactly do you mean by $-1<Fruit>? Can you put this in words?

lucas_zec
New Contributor

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

Sure!

I want to make the comparison with purchases that do not contain this category and/or brand. (Purchases B, C and E above). Therefore the $-1 sign.

This is the way I usually do, since there is 50+ categories in my database, so this is easier than naming all 50+ categories (especially since they change over time).

So ultimately: purchases with fruits/alfa have around 3 itens and 50$ revenue, whereas purchases without fruit/alfa have around 2 itens and 40$ revenue, Therefore, we should continue to offer this fruit/alfa category etc...

Thanks again!

MVP
MVP

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

But B and C both contains Category Fruit.... isn't it?

Capture.PNG

That is what confused me... I agree with E... but how is B and C meeting your criteria?

lucas_zec
New Contributor

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

They have fruits, but not fruits that are from brand Alfa, so they do not fit my criteria at this time.

I have read about p() and e(). I tried using the e(), trying to count purchases that do not met the criteria, but it didnt work also

lucas_zec
New Contributor

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

They do not fit my criteria -> therefore I want to get them to make the comparison with the others that do match the criteria (A, D and F).

Community Browser