2 Replies Latest reply on Aug 30, 2016 3:54 PM by Ivan Young

    How to count number of distinct customers who place Y to X orders in any given calendar week

    Emmanuel Bruschi

      Hello,

       

      I am trying to identify

      1. the number of customers who place:
        • Less than 5 orders a week
        • 5 to 10 orders a week

        • More than 10 orders a week

      2. the number of orders that belong to each bucket

      A week is defined here as starting on Sunday and finishing on Saturday.

       

      I have tried to read other posts about similar issues but all of them deal with running periods and I do not understand what they say, I have been at it for over 2 full workdays and making no progresses.

       

      I am able to create a view with the weeks and the distinct Distinct Count of customer IDs or Order IDs, but not the distinct count of order IDs per distinct Customer IDs.

       

      I attached a sample (made-up) copy of my data with the optimal views I would like to create on sheet 2. Can anyone help me do this?

       

      Thanks!

       

      Emmanuel

        • 1. Re: How to count number of distinct customers who place Y to X orders in any given calendar week
          chris.moore.11

          Hi, i took a try at this although i don't entirely understand the second chart your'e going for. Look at the attached workbook.

           

           

           

          I made a parameter where you must enter the date of the first date of "Week 1".

           

          The calculated field "Week" assigns a week number to them based on that parameter.

          Week:

          if [Order Creation Date] >= [First day of first week] and [Order Creation Date] < DATEADD('day', 7, [First day of first week]) then "Week One"

          elseif [Order Creation Date] >= DATEADD('day', 7, [First day of first week]) and [Order Creation Date] < DATEADD('day', 14, [First day of first week]) then "Week Two"

          elseif [Order Creation Date] >= DATEADD('day', 14, [First day of first week]) and [Order Creation Date] < DATEADD('day', 21, [First day of first week]) then "Week Three"

          elseif [Order Creation Date] >= DATEADD('day', 21, [First day of first week]) and [Order Creation Date] < DATEADD('day', 28, [First day of first week]) then "Week Four"

          elseif [Order Creation Date] >= DATEADD('day', 28, [First day of first week]) and [Order Creation Date] < DATEADD('day', 35, [First day of first week]) then "Week Five"

          else "Other" end

           

          The calculated field "Orders per week" classifies people into the buckets you want.

          Orders per week:

          if {FIXED  [Customer ID], [Week] : COUNTD([Order ID])} < 5 then "<5"

          elseif {FIXED  [Customer ID], [Week] : COUNTD([Order ID])} < 11 then "5-10"

          else ">10" end

          • 2. Re: How to count number of distinct customers who place Y to X orders in any given calendar week
            Ivan Young

            Hi,

            The views can be created fairly easily, however, Tableau isn't designed to display data that doesn't exist so you will probably have to live with empty cells or look into a data padding solution which I don't do.  Attached is a workbook with examples.

             

            Regards,

            Ivan