3 Replies Latest reply: Feb 6, 2017 8:23 AM by Ramzi Manoubi RSS

    Set analysis, missing data

    Ramzi Manoubi

      Context: CRM, price registration

       

      Used fields:

      Price (= price)

      Last_Reg (Order of the registration 1 = 1st = most recent registration, 2 = 2nd = 2nd most registration

      Prod (= product)

      Cust (= customer = store)

       

      When visiting stores, sales reps register the price.

      I want to use following formula to find for a certain type of shop the average selling price for a product.

      In the If structure I check if this is the most recent registration, taking in account the active filters.

      If it is, we take in account the price, else not.

       

      Formula

        Avg({<Price={">0"}>}
        Aggr(
          if(
            Min(Last_Reg) = Min(total<Prod,Cust>Last_Reg), Price,  0
          ), Cust,
          Prod
        )
      )

       

      When I use this formula in a table with columns: prod, cust, price and this formula,

      I expect when there's a price registered, an average equal to the price and yet, there are some for which it is null.

      I don't understand how this is possible.

       

      If I need to clarify something, please ask. Any help/ideas is appreciated. Thanks.