
1. Re: How to display number of times a dimension appears?
Dimitri.B May 31, 2012 7:02 PM (in response to david.severski)1 of 1 people found this helpfulProvided 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)

2. Re: How to display number of times a dimension appears?
david.severski Jun 1, 2012 9:39 AM (in response to Dimitri.B)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 Jun 3, 2012 5:47 PM (in response to david.severski)Try saving, closing and reopening 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 Jun 5, 2012 2:07 PM (in response to Dimitri.B)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

Sales per person.twbx.zip 11.5 KB


5. Re: How to display number of times a dimension appears?
Jonathan Drummey Jun 8, 2012 3:00 AM (in response to david.severski)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 dropdown 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