6 Replies Latest reply on Aug 15, 2019 9:23 PM by Udit Chowdhry

    Distinct count of customer ids

    Udit Chowdhry

      Hi all,

      I want to calculate these three things below and display only in one view

       

      Sum of distinct customer id for a given date

      Sum of distinct customer id which have an issued today

      Sum of distinct customer id which have an issued in past

       

      You can use the superstore dataset for this query.

      So how it is possible if anybody knows these type of calculation so please respond to my query asap.

       

      Thanks,

      Udit

        • 1. Re: Distinct count of customer ids
          Manoj Lobo

          Hi,

           

          Try this,

          create a calculated filed as shown

          For the first questionc

          distinct customer id:

          { FIXED [Order Date]:COUNTD([Customer ID])}

          Use this filed and filter for a date to show the Sum of distinct customer id

           

          For the next two questions, you can use a calculated field to filter the values  as shown,

          Use the distinct customer id: calculation and filter the date using the below instead of choosing a filler date

          distinct customer id which have an issued today:

          [Order Date]=TODAY()

          OR

          [Order Date]={MAX([Order Date])}  (FOR recent date)

          Drag this to filter and select TRUE

           

          For the past date you can modify the above calculation as shown

          [Order Date]<TODAY()

          OR

          [Order Date]<{MAX([Order Date])}  (FOR recent date)

           

          You can also make use of parameter to show all of them as per selection

           

          Hope this helps!

           

          If this reply helps, mark it as helpful/correct

           

          Manoj

          • 2. Re: Distinct count of customer ids
            Zhouyi Zhang

            Hi, Udit

             

            try this

             

            1. Countd(if [your date field] = Date("2019-08-08") then [Customer ID] end)

            2. Countd(if [your date field] = Today() then [Customer ID] end)

            3. Countd(if [your date field] < Today() then [Customer ID] end)

             

             

            Let me know if you have question.

             

            ZZ

            1 of 1 people found this helpful
            • 3. Re: Distinct count of customer ids
              sander.binda

              If you want to pick that given date yourself by a filter then change the first calculation of Zhouyi Zhang to

              Countd(if [your date field] = [your date field] then [Customer ID] end)

              and use a filter on it. You need to put this one on a seperated worksheet because the datefilter can't be applied to the 2 other calculations.

              • 4. Re: Distinct count of customer ids
                Udit Chowdhry

                Hi,

                Thanks for your answer

                • Total Customers – Sum of distinct customer id for a given date
                • New Customers – Sum of distinct customer id which have an issued today
                • Repeat Customers - Sum of distinct customer id which have an issued in past

                If I want to calculate these three things then same calculation will be used ???

                Thanks,

                Udit

                • 5. Re: Distinct count of customer ids
                  Zhouyi Zhang

                  Hi, Udit

                   

                  It will depend on how your data looks like, my sample calculation is working for normal case, so if you can provide a sample workbook, it will be easier to say yes/no.

                   

                  ZZ

                  • 6. Re: Distinct count of customer ids
                    Udit Chowdhry

                    Hi,

                    I am not able to share the datatset. You can use Sample Superstore Dataset

                    And one more thing how to represent these three cases in one view Which chart is best suitable for this scenario

                     

                    Thanks,

                    Udit