2 Replies Latest reply on Aug 29, 2018 1:05 PM by susheela.hegde

    Distinct count For Lowest denominator

    susheela.hegde

      Hello,

       

      I have a requirement to get the distinct count of customer ID's (super store data) across each Shipping mode in any given month. If the customer falls under First class and Standard class shipping mode, then we need to count this customer in Standard class. So for this , I have created Rating Calculated field which gives the ranking among shipping mode. \

       

      Example,

      For the month of October,

      Month of Order        Date Ship Mode          Distinct customer Count

      October 2017         Same Day                     8

      October 2017         Second Class               22

      October 2017         First Class                    28

      October 2017         Standard Class            86

      Total                                                            144

       

      But distinct Count Of customers  for the month of October is 136. When I slice the data by shipping mode, my total has to be 136 and not 144.

      The reason for 144 is, Customer CA-12265 falls under First Class and Standard class and counts this as 2 but it should 1 and this customer ID should fall under Standard Class.

       

      Basically, for every given month/Date, I need to look for customer ID , ship mode and apply ranking on ship mode and get the distinct count.

       

      Can anyone help me with this Calculation?