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

[lucas_zec]

Hi there!

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

As an example:

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!

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

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

[lucas_zec]

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]

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]

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?

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

[lucas_zec]

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]

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