10 Replies Latest reply on Mar 30, 2016 11:03 AM by Rody Zakovich

# Open Question: What Calculated Fields were most useful when learning Tableau?

Hello Fellow Community Members,

I am working on a project revolving around Calculated Fields in Tableau, and I wanted to gather information from the Community.  If you have a moment, can you let me know what types of calculations were most useful to you as you were getting started (or even as you advanced) in learning Tableau?

The ultimate goal is to come up with great intermediate/advanced calculations that we can use to help demonstrate Calculated Fields and their use cases inside Tableau.

As an example, for me, I could share Graduation Rate as an example -- I had to use two calculated fields in Tableau to generate this value:

1) Count of Graduates: SUM(IF [Status]=="Graduate" then 1 else 0 END) -- this gives me the count of graduates

2) Graduation Rate:  [Count of Graduates] / COUNTD([Student iD]), where the denominator is my total count of distinct students

Please feel free to share any calculations and use cases, in as much detail as you want/can include.   Let me know if you have any questions, and thank you in advance!!

• ###### 1. Re: Open Question: What Calculated Fields were most useful when learning Tableau?

This will be interesting. Thanks for posting this Matt.

LOOKUP(SUM([Sales]), 0)

Note: I didn't come from a programming background. When I was first starting out I was confused by the use of the double equal sign ==. I spent a bit of time trying to come up with a Google search that would reveal the reason for using that. I never did find it, and finally just had to ask. Argh! You want to take just a small bit of confusion (aggravation) out of learning calculations, don't ever use a double equal.

Cheers,

--Shawn

2 of 2 people found this helpful
• ###### 2. Re: Open Question: What Calculated Fields were most useful when learning Tableau?

Hi Matt,

I've been working on building out a training on this very topic, here are some of mine:

record level calculations like datediff & string calcs

how to deal with Nulls

creating dates

DATEPARSE
DATE, DATETIME

MAKEDATE

DATEADD and INT for numbervalues like 20160216

basic ratio calcs like SUM(Profit)/SUM(Sales)

why ratio calcs break depending on vizLOD

table calcs or LODs to the rescue

boolean logic

[Region] = 'West'

instead of IF [Region] = 'West' THEN 'Show' ELSE 'Hide' END

each condition needs to return True, False, or Null

IF [Region] = 'West' OR [Region] = 'East' THEN ...

*not* IF [Region] = 'West' OR 'East' THEN ...

how CASE statements don't work like MSSQL

embedded IF statement like the example you gave

why IF statement breaks depending on vizLOD

table calcs or LODs to the rescue

Tableau's Top 10 Table calculations, my the Next N table calculations post, the top 15 LOD expressions post.

The list goes on and on...

Jonathan

2 of 2 people found this helpful
• ###### 3. Re: Open Question: What Calculated Fields were most useful when learning Tableau?

Hi Matt,

Difference between:

MAX([Sales])

MAX([Sales],[Profit])

MAX(SUM([Sales]),1000000000000)

MAX(SUM([Sales]),SUM([Profit]))

Yours,

Yuri

• ###### 4. Re: Open Question: What Calculated Fields were most useful when learning Tableau?

Hey Matt,

I have a similar response as Jonathan. I think Row Level Calcs are very important, specifically those related to Dates.

*Most* visual representations of data deal with Time in one form or another. Whether we are looking at a Current Value, Values over Time, Comparing values from different time intervals, etc.

So I think understanding Date functions is very important.

For example,

How to create Dates : MAKEDATE(), DATE(), DATEPARSE()

Different ways of Truncating Dates: DATETRUNC ('year', 'quarter', 'week', 'day') vs DATEPART vs Custom Dates vs ( YEAR(), MONTH() ).

Datepart values from the Dropdown, are not the same as the DATEPART() function: When selecting a DATEPART option from the dropdown, Tableau still retains its' DATE Properties because it is not a new column, but rather a manipulation of the existing Field. This is what allows us to "Show Missing Values" when using a DATEPART option from the Dropdown. The DATEPART() function, creates a new Column/Field that is Numerical, and distinct from the Original DATE Field, so we don't get the same functionality.

But Tableau tried to trick you when you double click on it

TODAY(): Using the TODAY() function to created Period to Date calcs in combination with the Calcs listed above

Tableau's Epoch Date: How Tableau converts INT to DATES and Vice-Versa

Parameters with Dates: How to use Parameter Dates, or Period of Times in calculated fields.

There are a lot more, but this is a general list.

Hope this helps,

Regards,

Rody

2 of 2 people found this helpful
• ###### 5. Re: Open Question: What Calculated Fields were most useful when learning Tableau?

Very timely topic, as I'll be co-teaching Tableau to some co-workers next week.

Understanding INDEX() vs Rank formulas is something that everyone should know, especially when trying to look at things like measuring quarters from consecutive years, or only a specific set of quarters based on a selection.  Yes, it's a little advanced, but sometimes you get thrown something that seems relatively easy when you're new at this.

Window calculations - most people are trying to replicate items that come from Excel spreadsheets, and knowing window calculations definitely helps.

Using IF and IFF and the differences (including ELSE and ELSEIF in this).  And ZN - because that's a really great little hidden gem.

2 of 2 people found this helpful
• ###### 6. Re: Open Question: What Calculated Fields were most useful when learning Tableau?

G'day from Oz land

I come from an analytical area, not commercial like retail etc.

I am a self-taught tableau user (Statistics is my background so pretty numbers-savvy). I did look at the tutorials but I learned more by figuring things out myself.  This means that intuitive tools always win the day for me. Tableau is not particularly intuitive for me when you go past the basic drag and drop fields so making the most used processes more user-friedly will be a good thing.

We definitely use a lot of percent of total (across and down) as well as within groups. For those that are not that data savvy, understanding the difference and knowing you have done the right thing can be tricky.

I think some clarity about calculated fields using more than one dataset would also be useful.

Cheers

1 of 1 people found this helpful
• ###### 7. Re: Open Question: What Calculated Fields were most useful when learning Tableau?

INT()

FLOAT()

STR()

Type conversions are something I do regularly in Tableau, due to the occasional odd structure of data coming in via a live connection, or due to a calculation, and they can be lifesavers when you're trying to get mark colors set up based on a specific number or range of numbers.

2 of 2 people found this helpful
• ###### 8. Re: Open Question: What Calculated Fields were most useful when learning Tableau?

I do quite a bit with timeliness metrics and process monitoring dashboards.  My most frequently used calc, and one of the first ones I ever used, is DATEDIFF()

• ###### 9. Re: Open Question: What Calculated Fields were most useful when learning Tableau?

I am very new to Tableau with no programming background. My strength is I know what I don't know and my Google Fu' is pretty strong so I can eventually get what I need. These are the ones that I had to figure out my first few days. I am sure I will be coming back here often.

--------------Returns Age--------------

DATEDIFF('year',[DOB],TODAY())-1

ELSE

DATEDIFF('year',[DOB],TODAY())

END

--------------

--------------Returns a String for Facility Name from a 'Facility Code' also used to return a String for Region--------------

IF [Program Code] = 757 THEN "Escambia"
ELSEIF [Program Code] = 758 THEN "Bay"

<insert 20 more rows>

ELSE NULL
END

--------------

• ###### 10. Re: Open Question: What Calculated Fields were most useful when learning Tableau?

Hey Randy,

For Returns a String for Facility Name from a 'Facility Code' also used to return a String for Region

You may find that using a Case statement may yield better performance. For this type of function, I have found CASE *Generally* runs faster

CASE [Program Code]

WHEN 757 THEN 'Escambia'

WHEN 758 THEN 'Bay'

<Insert 20 more rows>

END

For IF/ELSE and CASE Statements, you don't need to explicitly write out "ELSE NULL", simply ending the calc with END after the WHEN (OR ELSEIF) statement will generate a NULL for all that don't qualify the logic.

Regards,

Rody

1 of 1 people found this helpful