2

Is there a way to count unique numbers in a column in Tableau?

COUNT(IF [Type] = "Boxes" AND [Ordernumber] is unique THEN 1 END)

In my data, several rows have the same order number. I want all the rows where [Type] = "Boxes" AND have a unique order number. Example:

Type Ordernumber
Boxes 10000000
Boxes 10000000
Boxes 10000001
Misc 10000002
Boxes 10000003

Should be equal to 2.

AnilGoyal
  • 25,297
  • 4
  • 27
  • 45
Jens
  • 183
  • 1
  • 10

3 Answers3

1

Let us assume the following data-

enter image description here

  • Now add the following calculated field say dcount with this calculation
{FIXED [Type]: COUNTD([Ordernumber])}

This calculation will return unique count for each type, like this..

enter image description here

  • Now perhaps (I am not sure) you want to exclude all duplicate values (as in your question you have written the desired output for boxes is 2), create this calculated field say unique_count
IF 
{FIXED [Type], [Ordernumber] : COUNT([Ordernumber])} > 1
THEN 0 ELSE 1 END

Now compare the results of both fields

enter image description here

AnilGoyal
  • 25,297
  • 4
  • 27
  • 45
  • Thank you! I got got the unique count. But I don't want the other stuff. Only one number, all the Boxes with an unique order number. – Jens Feb 23 '21 at 16:22
  • Glad that it helped. Kindly accept and upvote as usually desired on SO. For boxes count with an unique order number, just remove all the extra fields from view and add a filter for filtering boxes only. – AnilGoyal Feb 24 '21 at 04:39
1

Here’s one approach, relatively easy.

  1. Put Type on the filter shelf, select only boxes, and (important) add it to the context (right click on the field on the filter shelf to access the menu). Filters that are part of the “context” are applied early in the order of operations.
  2. Put OrderNumber on the filter shelf. On the filter dialog, go to the Condition tab and choose COUNT([OrderNumber]) = 1.
  3. Now your data is filtered as desired. The data that passes the filter will have one record for each order number that only has one record -- after you first ignore any data rows that don't reference boxes.

Note, it is possible that you want different semantics regarding the Type="boxes" filter. Perhaps you want to first eliminate orders with multiple rows and then afters only view the records with boxes. If so, experiment with changing which filter(s) is (are) in the context to see the impact of applying the filters in different orders.

Alex Blakemore
  • 11,301
  • 2
  • 26
  • 49
  • Thank you! It worked. But the result is shown as "Abc". I want to show the number only. When you hoover the mouse over it, the tooltip says 893, which is the correct number. But how do I show it? – Jens Feb 23 '21 at 16:18
  • With versions of Tableau prior to 2020.2, you would put SUM([Number of Records]) on a shelf such as columns or text. With more recent versions of Tableau, use a field that looks like Orders (Count) in italics. – Alex Blakemore Feb 24 '21 at 17:23
0

You can use the COUNTD() function to achieve this. Say your numbers are in a column called "Number", you should create a calculation as COUNTD([Number]).

This will return a value of 5 for the example you have given.

reusen
  • 491
  • 3
  • 11
  • Thank you! I got all the unique order numbers. But I realise I asked the wrong question. I'll update the original question! – Jens Feb 21 '21 at 12:40