4 Replies Latest reply on Mar 12, 2012 3:52 AM by Daniel Thull

# Counting unique values -- why doesn't the formula work?

Hi,

I've been researching on this topic for some time but can't seem to get to the bottom of it...

I have data in an Excel file, so I cannot use the COUNTD method to calculate unique values in a long list. I was really glad when I found this thread. However, when I tried to use the provided formula with my data (see attached workbook) it didn't work.

WINDOW_SUM(IIF(FIRST()==0, 1, IIF(ATTR([Brand])==LOOKUP(ATTR([Brand]), -1), 0, 1)))

In Excel I found that I have 247 unique brands, in Tableau I cannot get this to work as it always counts the total number of rows. What am I doing wrong? Can anyone help?

Thanks!

Daniel

• ###### 1. Re: Counting unique values -- why doesn't the formula work?

Hi Daniel,

To make this work you need to have the variable you want to count on available to Tableau in the level of detail of the view (somewhere on the Rows, Columns, Color, Size, or Level of Detail shelves), and the calculation set to compute along that variable.

Here are three steps to do this:

1. Add Brand to the Level of Detail shelf. This pops up an overlapping text message, ignore it.

2. Click on the green "Number of brands" pill and select Compute along->Brand from the drop down menu. This will cause the calculations to show the correct total.

3. Edit the calculation like so to get rid of the overlapping text:

IF FIRST()==0 THEN

WINDOW_SUM(IIF(FIRST()==0, 1, IIF(ATTR([Brand])==LOOKUP(ATTR([Brand]), -1), 0, 1)), 0, IIF(FIRST()==0, LAST(),0))

END

That bit of magic comes from Richard Leeke's post here: http://www.clearlyandsimply.com/clearly_and_simply/2011/01/another-look-at-site-catchment-analysis-with-tableau-6-part-3.html

Does this work for you?

Jonathan

• ###### 2. Re: Counting unique values -- why doesn't the formula work?

I am not sure what your goal is, or how you plan to use this (may need additional steps depending on your final display), but here is how I would approach this:

1. create calc field:

IF FIRST()==0 THEN SIZE() END

2. place Brand on Level of Detail shelf

3. set Compute using to Brand

you can see an example of this in the attached.

1 of 1 people found this helpful
• ###### 3. Re: Counting unique values -- why doesn't the formula work?

Hi Daniel,

If you use the Extract Data option within your workbook you can use the COUNTD formula

1 of 1 people found this helpful
• ###### 4. Re: Counting unique values -- why doesn't the formula work?

Thanks everybody! Really appreciate your help!

Daniel