5 Replies Latest reply on Sep 12, 2012 11:22 AM by Kevin Andrist

    Running total using COUNTD

    Kevin Andrist

      Please take alook at the attached image-screenshot.


      What you're seeing is a summary of customers, customer sales, % of total sales, cumulative % of sales..by customer rank ascending. The ranking assignment exists is excel & is not calculated in Tableau. 


      On the far right, second col from the right, I'm using COUNTD to determine the unique SKUs by customer. The last col on the right is an attempt to determine the cumulative unique SKUs.  As you can see, I'm getting just a running total of unique SKUs by customer (ex. 746 + 46 = 792.  792+654=1446..etc.)  This is calcualting as expected, but I'd like to develop a running total of unique SKUs of the set (customer 1) , then unique SKUs in the set (customer 1 + customer 2), then unique SKUs in the set (customer 1 + customer 2 + customer 3).


      (FWIW..In the (customer 1 + customer 2) case the unique SKUS are actually 758, rather than 792.)


      I can supply data if need be, but the file is very large & I'd have to break it down into a non-proprietary small example(= time..which is short)..I'm hoping the calc I need is simple (don't we all.. :-) ) & just not known to me..yet.


      Any ideas?



        • 1. Re: Running total using COUNTD

          Hi Kevin,


          I am not sure if I have understood your requirement correctly - if I have not, please help me understand and am happy to help if I can! ;-)


          I think you need to look at defining the Partitioning & Addressing fields correctly (online help available here), and then use the "Restarting Every" option correctly to arrive at the result you are looking for. See the image attached.


          Does that help?




          • 2. Re: Running total using COUNTD
            Kevin Andrist

            Thanks Siraj,


            I see what you're saying(I think), but it seems I have very few options..I only have three:  Table(down), Cell, Customer Name.  Choosing them only gives me replicates of Table(Down) or just the COUNTD calc (second col from right).


            I'm not familiar with "Restart Every"..but it sounds promising..


            I can get unique skus in combined customers by creating various Groups..but this is very tedious & not at all flexible.


            I don't see an image attached..



            • 3. Re: Running total using COUNTD
              Kevin Andrist

              OK..I see the attachment now.  I did look into Advanced-Compute Using, but the only option I have it Customer Name & it's on the right side (it is being used..).   No other options are available.


              • 4. Re: Running total using COUNTD
                Jonathan Drummey

                Hi Kevin,


                As Siraj noted, it's a matter of partitioning, however I can't think of a way to get this using normal partitioning. The issue is that in order to do the running sum of the count of distinct SKUs across the "set" of customers so far in the table, each customer "row" in the output needs to have access to all of the prior rows in the data in order to identify the unique SKUs. An alternative way of putting this is that the first row in the data needs to be checked for a unique SKU N times, where N is the number of customer rows. So we need to have access to all the data for most every row in the output. When just Customer is in the view, each Customer row can only "see" the SKUs for that customer, so the running sum won't work. Therefore, SKU needs to be in the view as well.


                In the attached, there are 4 table calculations that are nested together. The latter three might be able to be optimized into fewer calcs. The calcs are:

                1. Raw Count of SKU - with a Compute Using of SKU, Customer, sorted by SKU/Minimum/Ascending this generates the count of distinct SKUs.

                2. Total SKU per Customer - Nested Computed Using of SKU, this identifies how many unique SKUs are for the customer.

                3. Running Sum of SKU - Nested Compute Using of Customer, this generates the actually running sum of SKU. However, it returns one row per SKU, which would create overlapping text.

                4. Final Running Sum of SKU - Nested Compute Using of SKU, returns only one row per customer.


                Note that for you to include this in the worksheet that you attached the screenshot of, most all the other calcs will need to be modified to not return overlapping text.





                • 5. Re: Running total using COUNTD
                  Kevin Andrist

                  Thanks Jonathan..I'll give your approach a spin and see if I can get it to work.