5 Replies Latest reply on Jun 8, 2012 3:00 AM by Jonathan Drummey

    How to display number of times a dimension appears?

    david.severski

      Hello, everyone. I'm a Tableau beginner with hopefully a straightforward question. I have a flat file list of entries and am trying to display the frequency distribution of the entries.

       

      The data looks like:

       

      SalespersonSale Size
      Joe54
      Joe42
      Joe55
      Sally60
      Sally50
      Mark20
      Mark100

       

      The end result will be the following sort of information:

       

      #er of sales
      # of salespersons
      21
      33

       

      How can I do this in Tableau?

        • 1. Re: How to display number of times a dimension appears?
          Dimitri.B

          Provided you data is not in Excel or Access (both have limitations due to Jet SQL), you can use calculated fields:

           

          COUNT([Salesperson]) //number of sales, assuming each row is one sale

          COUNTD([Salesperson]) //number of salespersons, i.e. count each person once (distinct count)

          1 of 1 people found this helpful
          • 2. Re: How to display number of times a dimension appears?
            david.severski

            Thanks, Dimitri. My data is in Excel and CSV files so the countd function isn't available. The online help mentions that using an extracts is supposed to enable this functionality, but extracting the data and verifying that the extract is being used doesn't seem to turn that function on. Is there way to get countd to work in this scenario?

            • 3. Re: How to display number of times a dimension appears?
              Dimitri.B

              Try saving, closing and re-opening the workbook. COUNTD() should work with extracts, if it doesn't for you - can you attach a sample workbook?

              • 4. Re: How to display number of times a dimension appears?
                david.severski

                Thanks, Dimitri. I've been able to get the COUNTD function to work (closing and reopening the workbook was the key). While I've got the two calculated fields in place, I'm not able to replicate the summarized crosstab from my initial post. I'm attaching a packaged workbook if you (or anyone else) have the chance to take a look and steer me in the right direction.

                 

                Thanks for the help!

                 

                David

                • 5. Re: How to display number of times a dimension appears?
                  Jonathan Drummey
                  Hi David,
                  If we had an existing dimension in the data to do find the # of salespersons over, then we could use the basic COUNTD() to get results.
                  However, this gets a little tricky because the dimension you want to count sales over (the number of sales) is actually a measure. So we have to turn that measure into a discrete value that we can count records over. But if we use the COUNTD() at that point we get overlapping records that all equal 1.
                  (Note that this can work fine in a in a bar chart, as you can see in the bar chart worksheet since Tableau will stack the returned records instead of overlapping them).
                  To get this to work, we need to resort to a table calculation to return the correct results. Here's the basic process to get the desired table:
                  - Drag the Number of Sales (COUNT([Sales Made])) measure to the Rows shelf, set it to Discrete (a blue pill). This shows 1 row with 9 for a value.
                  - Drag Salesperson to the Level of Detail shelf. The table now shows 2 rows, one for each value of Number of Sales.
                  - Click once more on the blue Number of Sales Pill on Rows once more and turn off "Ignore in Table Calculations". This will let us partition the # of salespersons calculation on the Number of Sales.
                  - Create a calculated field (I called it TC # of Salespersons) with the following formula:
                  WINDOW_SUM(COUNTD([Salesperson]))
                  - Drag that new field to the Rows shelf, then click on the pill twice to set the Compute Using to Salesperson, and set it to Discrete.
                  - If you want, then you can get rid of the borders for the Abc area and Click on the Color drop-down to set the Transparency to 0% to hide the Abc. I set up the table this way because it's the easiest to get both of the headers.
                  Cheers,
                  Jonathan