3 Replies Latest reply on Nov 30, 2018 5:08 AM by Jim Dehner

    LOD Expression that satisfies multiple conditions

    Lusiana Djie

      Hi guys, I have table viz containing the `customer_id`, list of `order_id` that the customer has placed,  `order_date` sorted ascendingly as well as `order_rating`.

       

      On the filter pane, I have these filters applied:

      - `order.status` = 'completed'

      - `order.is_vip_order` = True

      - `order_rating` to exclude NULL values

      - `order.country_code` shown to enable the user to interact with the filter

       

      I wanted to extract the latest date of the order placed by the customer. Tried to play around with LOD expression but none of them gave what I wanted. So these are the expressions that I have tried:

       

      1) {FIXED [customer_id] : MAX(order_date)}

      • This gave me the latest date of order placed by the customer, ignoring all the filter conditions that I have applied. This is not what I'm expecting.

       

      2)  {FIXED [customer_id :

              IF [order_rating] IS NOT NULL

                AND [is_vip_order] IS TRUE

                AND [status] = 'completed'

             THEN MAX([order_date])

             ELSE NULL END }

      • I thought by adding some conditions into the LOD expression it would help me arrive to the expected result. This calculation is not valid . The problem with this is that some of the conditions in the IF statement didn't work. However, if I stripped off the first and second conditions which left me with:

            

             {FIXED [customer_id :

                  IF  [status] = 'completed'

                 THEN MAX([order_date])

                 ELSE NULL END }

       

       

          This works! But this is not what I wanted because I want the other conditions to apply as well.

       

      Would anybody be able to help me with this? Thank you!