KNOWLEDGE BASE

Counting Occurrence of a Given Value in a Field


Published: 06 Oct 2016
Last Modified Date: 17 Jul 2017

Question

How to count number of occurrences of a given value in a field.

Environment

  • Tableau Desktop 10.0

Answer

Using the Sample - Superstore data source, the below example will show how to create: 
  1. Within the Furniture Category, how many tables were ordered. 
  2. Within the Furniture Category, how many tables were ordered in 2015.
  3. Within the Furniture Category, how many tables were ordered in 2015 and the sum of sales was more than 1000 dollars.
Modify the below calculated field: 
SUM ( INT ( [field name] = "desired value" ) )

Step 1: Create field to show the number of tables ordered

  1. Select Analysis > Create Calculated Field 
  2. In the Create Calculated Field dialog box, do the following and click OK:
  3. Name the field. In the example, this field is called "Number of Table Orders" and enter the following formula: 
    SUM ( INT ( [Sub-Category] = "Tables" ) )

Step 2: Create field to show the number of tables ordered in 2015

  1. Select Analysis > Create Calculated Field 
  2. In the Create Calculated Field dialog box, do the following and click OK:
  3. Name the field. In the example, this field is called "Table Orders in 2015" and enter the following formula: 
    SUM ( INT ( [Sub-Category] = "Tables" AND YEAR([Order Date]) = 2015 ) )
    

Step 3: Create field to show the number of tables ordered in 2015 > $1000

  1. Select Analysis > Create Calculated Field 
  2. In the Create Calculated Field dialog box, do the following and click OK:
  3. Name the field. In the example, this field is called "2015 Table Orders > $1000" and enter the following formula: 
    SUM ( INT ( [Sub-Category] = "Tables" AND YEAR([Order Date]) = 2015 AND [Sales] > 1000) ) )
    

Step: Build the view

  1. Drag Category to Columns
  2. Drag Sales to Rows
  3. Drag the calculated fields to Details 

Additional Information

Typecasting a Boolean as an integer returns 1 for a True and 0 for a False. If the provided solution does not yield the desired result, there may be a level of detail issue. This calculation occurs at the view level of detail, unless it is modified.

To view these steps in action, see the video below: 
Did this article resolve the issue?