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

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

Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
lucas_zec
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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!

sunny_talwar

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
Contributor II
Contributor II
Author

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!

sunny_talwar

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
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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