1 Reply Latest reply on Nov 20, 2018 10:23 AM by Michel Caissie

    Filter Orders Containing Product X and Product Y

    alexander.moshovitis

      Hi All,

       

      I would like to see all orders containing apples and oranges.

       

      I tried the below but it doesnt work:

       

      {FIXED [Order Id]:IF [Product Name] = 'Apple' AND [Product Name]= 'Orange'

      THEN "Bundle"

      ELSE ""

      END}

       

      I get he following error: result of a level of detail must be an aggregate.

       

      Thanks!

        • 1. Re: Filter Orders Containing Product X and Product Y
          Michel Caissie

          Alexander,

           

          First  [Product Name] = 'Apple' AND [Product Name]= 'Orange' will always return false.  The inside of the lod is a row calculation and a Product Name cannot have  two different values.

          Next you need to aggregate the inside of the lod. And since your calculation returns a String you would need to aggregate with ATTR  which is not allowed.

           

          Anyway, I would take the following approach

           

          orderIdHaveAtLeastOneApple

          {FIXED [Order Id] : MAX(if [Product Name] = 'Apple' then 1 else 0 end)}  = 1

          so for  each row of a given orderId, if  Apple then flag a 1.  If the MAX of this flag for that OrderId is 1 ,it means you have at least one Apple.

           

          orderIdHaveAtLeastOneOrange

          {FIXED [Order Id] : MAX(if [Product Name] = 'Orange' then 1 else 0 end)}  = 1

           

          orderHaveAppleAndOrange

          if orderIdHaveAtLeastOneApple AND orderIdHaveAtLeastOneOrange then 'Bundle' else '' end

           

          Michel