Calculated fields add data within Tableau, without changing the underlying data.
For example -> Ratio -> sum(x)/sum(y)
- Fields – for example – gdp, population, location,
- Functions – sum, min, max, avg
- Operators – /,+
- Parameter – variables to replace constant values
- Comments – //, not displayed in the views
There are many other ways to create different types of calculated fields:
- using different functions – avg([gdp]),
- breaking up a data field,
- combine data fields,
- or try any other solution.
You can create:
- calculated strings,
- calculated dates,
- logic fields,
- geographic data.
Calculation Editor
- open the calculation editor: right-click on a blank space in the Data pane, and select Create Calculated Field. This opens up the calcualtion editor.
- Calculated field name -> allows you to identify it quickly in the Data pane.
- On the right -> is a full list of all the functions that Tableau recognizes.
- Listed functions contains:
- Syntax – DATENAME(date_part, date, [start_of_week])
- Function description – Returns a part of given date as a string, where…
- Example calculation – DATENAME(‘month’, #2022-04-15#) = “April”
String Calculations
- String data is any data that is text, rather than numbers, dates, or locations.
- Concatenate strings is put it together.
- Operator +
- Example : [City] + ” , ” + [Region]
- Tableau cannot combine string and integer data.
- Data needs to be same data type.
- Tableau is read only, so any change is not written back to the database.
- Converting data type function:
- float, int, str
- STR( [example] )
- If you want to combine the String and Integer fields. You can concatenate the fields by using the str funtion to convert Integer to matching data type.
Example:
- Create a calculated field that combines the Customer ID and Customer Name fields:
- In the Data pane, to the right of Dimensions, click the drop-down arrow, and then click Create Calculated Field.
- In the top left text box, type “Customer Number & Name”.
- In the lower left white space, enter this formula:
- “Customer #” + STR([Customer_ID]) + ” – ” + [Customer Name]
- Click OK.
- Add the new field to the view to the left of Customer Name, and adjust so all of the text shows.
- From Dimensions, drag the Customer Number & Name fields to Rows, and drop it to the left of Customer Name.
- If a Warning window appears, click Add all members.
- In the view, adjust the first column’s width by hovering the mouse pointer over the right border of the values under Customer Number & Name, and then click and drag to the right.
- If you need to, adjust the row height by hovering the mouse pointer over the bottom row border, and then click and drag slightly down.
- Make sure the new field’s values use this format: “Customer #1887 – Wendy Hobbs”.
- Remove the original Customer Name field from the view.
- On Rows, right-click Customer Name, and then click Remove.
- Note that your row heights may automatically adjust themselves. Re-adjust them if needed.
Date-Specific Calculations
- calculating difference between dates,
- adding date
- using datetime field
- todo