4 Replies Latest reply on Aug 10, 2016 9:57 AM by Connor Harle

    Calculating Number of Records in Category

    Connor Harle

      I feel like I'm close to getting the answer to this, but I just can't get a complete grasp of how to solve my problem.

       

      I've created categories based on a continuous measure, and I want to see how many accounts fall into each category. For reference, the function that I used to create the categories is SUM(Actions)/SUM(Customer Lifetime in Days). The problem I'm having is getting Tableau to not aggregate the continuous measure when I try to get a count of the ID's per category (thus giving me all of the accounts in one category).

       

      Here's an example showing the categories and how Tableau is correctly categorizing them when the accounts are on the row level:

      Screen Shot 2016-08-09 at 1.49.31 PM.png

      And here's what happens when I change the Account Id rows to a COUNTD(Account Id), even when I uncheck aggregate measures:

      Screen Shot 2016-08-09 at 1.50.51 PM.png

       

      I've used number of records as well and run into the same problem. Conceptually, understanding how to fix this problem will be incredibly helpful for me in a lot of different cases.

        • 1. Re: Calculating Number of Records in Category
          Kaylan Hazlett

          I believe the measure you need to add is 'Number of Records' in order for it to give you a '1' where you currently have 'ABC' in your table. Once you do this, you can also Count(Number of Records) to tell you how many records are in each category. If account ID is unique, 'Number of Records' should basically be equal to 1 for each row of data, so if you sum or count that, it will give you a total for another dimension.

           

          Does that help?

          • 2. Re: Calculating Number of Records in Category
            Connor Harle

            Thanks for reaching out Kaylan. Unfortunately, Number of Records gives me the same results.

            • 3. Re: Calculating Number of Records in Category
              John Sobczak

              It's all about the level of detail in your view.  In your table you have the category dimension in your view so it forces Tableau to aggregate down to that level of detail.  In the bar chart view there is NO dimension category in the level of detail of the view so everything is aggregated together.  What is it you are trying to do with the bar chart?

              • 4. Re: Calculating Number of Records in Category
                Connor Harle

                The category dimension is also on the bar chart. I'm trying to find the number of records (or count of Account IDs) in each bucket of daily actions (10-19, <10, etc. The way I'm interpreting the problem is that when I put number of records in the view, it is aggregating all daily actions so that everything falls into the biggest bucket, instead of calculating the daily actions per record and then giving me the number of records in each category. I just can't figure out how to get it to do the latter.

                 

                For reference, I used a calculated fields to create groups based on the number of daily actions:

                 

                IF [Daily Actions] < 10 THEN "1-10"

                ELSEIF [Daily Actions] < 20 THEN "11-20"

                etc.

                 

                The formula in my original post is the calculated field I used to create [Daily Actions].