Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Join us March 10th, 7 Ways Modern Analytics Can Help You Take Smarter Action. REGISTER NOW
Showing results for 
Search instead for 
Did you mean: 
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

Have you tried this may be


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


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


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



May be try this?

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

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


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

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Contributor II
Contributor II


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!

Contributor II
Contributor II

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!


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?

Contributor II
Contributor II


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!


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


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

Contributor II
Contributor II

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

Contributor II
Contributor II

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).