14 Replies Latest reply: Aug 9, 2018 8:41 PM by Sunny Talwar RSS

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

    Lucas Machado

      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!