4 Replies Latest reply on Apr 6, 2016 8:55 AM by Edward Yeldham

    Grouping Counts

    Edward Yeldham

      Hello,

       

      Just starting on Tableau and looking for help with some data.


      I have customer numbers and order numbers across a time frame.  I have created a worksheet to count the number of orders each customer has for the relevant time window by using CNTD() on my "Header ID" field which is the unique transaction number each order has.


      I also have a customer account no field which is unique.

       

      Having totalled my CNTD() field I know the total number of orders each week.  I then have the detail behind this by individual customer to tell me how many orders each individual customer has had.


      What I would like to know is the number of customers who had 1,2 or 3 orders in a given time frame.

       

      My results would be something like this

       

      No Of Orders           Timeframe

      1                              52

      2                              102

      Total                         154

       

      I currently know there are 154 total unique orders ans also the breakdown of which customer has 2 orders and which has 1 order.

       

      Help appreciated and probably simple?!


      Ed

        • 1. Re: Grouping Counts
          Mark Fraser

          Hi Edward

           

          There is a lot going on in your question, but I'll try and help

          What I would like to know is the number of customers who had 1,2 or 3 orders in a given time frame.

          If you have v9, you can use LOD expressions.

          You'd want something like this...

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

           

          Check out Number 1 (and the others, very useful!!)

          Top 15 LOD Expressions | Tableau Software

           

          Happy to help further if I can...

           

          Cheers

          Mark

          • 2. Re: Grouping Counts
            Edward Yeldham

            Mark

             

            That is a very useful article and helps me (a bit).

             

            I have now created a calculated field as follows

             

            { FIXED [Ship To Account] : COUNTD([Header Id]) }  which is within the article.  I can see this gives me a good count as I wanted.  However it takes no account of the timeframe I then want to plot it over.


            It appears to count up all the orders in my data set over the entirety of time.  I have a filter on my shelf that says I'm only interested in the current year and then had plotted the weeks across the top.  Most of my customers will only order once per week, sometimes twice.

             

            Any thoughts?


            Ed

            • 3. Re: Grouping Counts
              Mark Fraser

              Hi Ed

               

              That's a start

               

              Background -

              LoD functions calculate at a lower level (against the data), hence the ignore the filters you applied in the View.

              Sometimes that behavior is useful, sometimes not. But we can work around it...

               

              This is a quick fix -

              IF YEAR([Order Date]) = 2016 THEN {FIXED [Customer Name] : COUNTD([Order ID])} ELSE NULL END

              We first test on the year, and only when it is true, return our counts.

               

              The problem with the above method, is that YEAR is fixed, it is hard coded and won't update, we dont want that.

              But we can fix that to - using the TODAY() function

               

              So something like this...

              IF YEAR([Order Date]) = YEAR(TODAY()) THEN {FIXED [Customer Name] : COUNTD([Order ID])} ELSE NULL END

               

              We now dynamically take the year from the system date and use that to test against.

              If we wanted last year, then add -1, 2 years ago -2 and so on...

               

              These are REALLY useful concepts.

              If you can master IF (logic statements) and LoD you'll be well on your way

               

              Let me know if anything isn't clear or you have any questions

               

              Cheers

              Mark

              • 4. Re: Grouping Counts
                Edward Yeldham

                Mark

                 

                Thanks for this but I'm now getting slightly odd results as shown below as rather than show the no of orders as a count in the first column it is showing the total instance of orders for a given customer (I assume) and then plotting info associated with this customer across in the weeks fields.  I had wanted to see something like below example

                 

                 

                 

                                                             Week No         

                No Of Orders per store         Wk 1        Wk  2        Wk  3     Wk     4      Wk       5

                1                                             1               3               3              1              1                   

                2                                             0               0               2              1               0

                3                                             0               1               0               2               2

                Total Orders                           1               6               7               9               7

                 

                The total orders would (in excel) be a sumproduct.