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

This looks right to you?

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]

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]

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)