Category Archives: Calculated Field

Distance Calculation

I was tasked with an interesting request recently at work. It was to find how many hotels of an external brand were near one of our brands’ hotels. For those not familiar with the hotel industry, we leverage a data provider called STR. So no crazy web scrapers needed. We have all the hotel data required for the task.

The key fields required here are a unique identifier for each hotel and their respective latitude and longitude. My resulting data would need to have a row of every external brand hotel for each internal brand hotel. First, I created two tables in the database. One for all internal and the other for the external brand. Querying both tables together without specifying a join relation results in a row of every external brand hotel for each internal hotel. The only thing I needed to add was a calculation to determine the distance between two lat/lon coordinates.

We’re a Vertica shop. So I checked the reference guide and found the DISTANCEV function. It is as simple as passing in all four coordinates and it returns the distance in kilometers. I just multiplied that by 0.621371 to convert to miles. The task was to find number of external hotels within a 2 mile and 7 mile radius per internal hotel. There are a number of ways to get this but I decided to use Tableau. You never know when requirements will change and a viz will then be asked for. I pass this as a custom SQL query in Tableau’s data window.


The output needed to be a list of our internal hotels with two columns. One counting the number of external hotels within 2 miles and another within 7 miles. Using Tableau’s Level of Detail function, I created the following.

if {fixed [choice_id], [external_str_nbr] : sum([distance])} <=2 then 1 else 0 end

For every unique pair of internal and external hotel ids, I check the distance between the two and if it is less than or equal to 2, I sum 1. This is repeated with less than or equal to 7 calculated field.

My end result is as follows:


The way I have this setup, under 2 miles are also included in under 7. This was a fun task. I’ve never done anything related to distance measurement. Luckily, there are tools that make it so simple.


Share Button

Tableau in the office – episode 3

I’ve been revising some old dashboards at work that definitely are due for a refresh. One of the updates is to demonstrate Year over Year for a given metric. I wanted the calculation for current year to compare Jan 1 to date against previous year. But if the user selects to view YoY of previous years, I wanted to show full year difference, not Jan 1 to date. Below was my approach.

For sample data, I created an Excel file with random values per date from 1/1/2011 to 5/31/2015. You can download a copy of it here. To get my desired result, it will take one parameter and three calculated fields in the Filter shelf.

First, I created a Parameter for Year.

ScreenHunter_01 May. 20 16.07

Then I created a formula to only show the year selected in the Parameter and the year before it. This is a boolean expression and I place it on the Filter shelf, selecting the True result.

[Year] = year([Date]) or [Year] – 1 = year([Date])

In my scenario, I only want to show YoY of a year at a time. And since YoY is created with a table calculation for percent difference from previous, I needed a way to keep the result but hide the previous year in the view since it is blank. I used a simple index() calculation and placed this in the Filter shelf, selecting the value “2”.

Next, the following formula creates a calculated field which again is used in the Filter shelf. I’ve broken down the formula with the //comments.

//If a data point’s date is this year and in past months, keep it.
if [Year] = datepart(‘year’, today())
and datepart(‘month’,[Date]) < datepart(‘month’, today())
then ‘keep’
//if a data point’s date is in this year and in current or future months, filter it.
elseif [Year] = datepart(‘year’, today())
and datepart(‘month’,[Date]) > datepart(‘month’, today())
then ‘filter’
//if a data point’s date is in past years, keep it.
elseif [Year] < datepart(‘year’, today())
then ‘keep’
else ‘filter’

Place this calculated field in Filter shelf and select “keep”. Here are the three calculated fields in the Filter shelf. Any field I create strictly for filtering, I name with a “f.” prefix so I know its intended purpose.

ScreenHunter_02 May. 21 09.57

Now I place the Units measure in the Text shelf, and change the Sum(Units) to a Percent Difference table calculation.

ScreenHunter_03 May. 21 10.02

I also created a “label” field so the text changes depending on the Year parameter. This is to make it clear to the audience that YoY for current year is a Jan 1 to date comparison over previous year. But previous year to previous year minus 1 comparisons are Full Year over Full Year.

if [Year] = datepart(‘year’,today()) then “YoY” else “FYoFY” END

Add this “label” field to the Text shelf and lastly, add Year(Date) to the Text shelf too. Once all my text fields are in place, I edit the label as follows:

ScreenHunter_06 May. 21 10.09

The final result is below. Change the Parameter to view the differing YoY or FYoFY calculations. I included a table with the sample data.

Thanks for tuning in!

Share Button

Tableau in the office – episode 2

Time for another Tableau in the Office post. A quick viz that I worked on was for a department head that wanted to see how much a service had declined in recent years. The decline is due to advances in technology, therefore the service becoming obsolete. I took a look at the data (sample data used here) and created a simple line chart by plotting Quarter (Continuous) with Quantity.

ScreenHunter_01 Jan. 31 15.22

This is pretty straightforward. But I wanted to show the total percent decrease for 2014 Q4 since 2013 Q1. This is where I employed a Table Calculation. Table calcs in Tableau are extremely powerful. They can range for very simple to exceptionally complex. I try to read as much as I can about table calcs and encourage others as well.

I created a calculated field that used the LOOKUP function. This is a great way to isolate exactly what is needed as you can specify the offset. The formula is written as LOOKUP(expression, offset). So LOOKUP(SUM(QTY), FIRST() + 3) would return the fourth value in my partition.

Since I only needed the first and last values, I didn’t require an offset. Here’s how my formula looks:

ScreenHunter_02 Jan. 31 15.33

In pseudocode: (QTY at 2014 Q4 – QTY at 2013 Q1)/ QTY at 2013 Q1

I formatted the value as a percentage and placed this calculated field in the Detail shelf so I can use it in the chart.

ScreenHunter_03 Jan. 31 16.02

Right click on the last mark, select Annotate > Mark

ScreenHunter_04 Jan. 31 16.05

Edit the annotation as desired.

ScreenHunter_05 Jan. 31 16.06

Here’s my final viz. Simple and effective in illustrating the decrease of a service over the last two years.

ScreenHunter_06 Jan. 31 16.08

Thanks for tuning in!

Share Button

Time Duration in Tableau

Time conversion seems to be pretty popular topic for Tableau. I recently worked on a project where the users wanted to display media duration in hh:mm:ss format. I looked around the Tableau forum and saw others have had this issue too. As long as the duration does not exceed 24 hours, this formula works just fine DATETIME(SUM([total dur secs]/86400)). And then use Tableau Date formatting to change to hh:mm:ss. But if duration will exceed 24 hours, this formula will not work. In my case, duration will exceed 24 hours. So here’s how I tackled this problem.

First, I needed to calculate duration in seconds before I could create the required format. Working in media, time codes go down to the frames level. So first I converted frames into aggregated seconds based on the asset frame rate, [total dur secs] =  SUM([PROGRAM_DURATION]/29.97)

Now that my [total dur secs] field is in total seconds we can begin. There are probably many ways to perform this but this was the approach I took.

First, I created a [total hours] calculated field.

IF INT([total dur secs]/60/60) >=1 THEN
STR(IIF(INT[total dur secs]/60/60)> 9, “”, “0”)) + STR(INT[total dur secs]/60/60))

The first line tests if the duration is greater than or equal to one hour. If that is true, then the second line tests if the duration is greater than 9 hours. If that is true, then create a blank, if false, then pad with a zero. Since I’m concatenating, the duration is converted to strings. If the total duration is not greater than an hour, then blank.

Next, I created a [total minutes : seconds] calculated field.

IF INT([total dur secs]/60) >=60 THEN
STR(IIF((INT([total dur secs]/60)-INT([total hours])*60)> 9, “”, “0”)) + STR(INT([total dur secs]/60)-INT([total hours])*60)
+ “:” +
STR(IIF(ROUND([total dur secs]INT([total dur secs]/60)*60)> 9, “”, “0”)) +
STR(ROUND([total dur secs]INT([total dur secs]/60)*60))
ELSEIF INT([total dur secs]/60) >=1 THEN
STR(IIF(INT([total dur secs]/60)> 9, “”, “0”)) + STR(INT([total dur secs]/60))
+ “:” +
STR(IIF(ROUND([total dur secs]INT([total dur secs]/60)*60)> 9, “”, “0”)) +
STR(ROUND([total dur secs]INT([total dur secs]/60)*60))
ELSE “00:” + STR(IIF(ROUND([total dur secs])> 9, “”, “0”)) + STR(ROUND([total dur secs]))

Let’s break down what is happening in the calculated field. The following portion below checks if [total dur secs] is greater than or equal to 60 minutes. Because I want time formatted as hh:mm:ss and I don’t want the minutes portion to exceed 60. So I subtract out [total hours] as minutes. Again, using the IIF to pad the extra zero as needed.

IF INT([total dur secs]/60) >=60 THEN
STR(IIF((INT([total dur secs]/60)-INT([total hours])*60)> 9, “”, “0”)) + STR(INT([total dur secs]/60)-INT([total hours])*60)

The next section below is calculating the seconds portion of the duration.

STR(IIF(ROUND([total dur secs]INT([total dur secs]/60)*60)> 9, “”, “0”)) +
STR(ROUND([total dur secs]INT([total dur secs]/60)*60))

I have to round [total dur secs] because I don’t want decimals. Then similar to the minutes calculation, I want to subtract out minutes that exceed 60. The IIF again pads for zeros as necessary. 

The ELSEIF and ELSE basically repeat the same calculation, checking if minutes are greater or equal to 1 or zero respectively.

Lastly, I bring [total hours] and [total minutes : seconds] together in a new calculated field, [Total Duration].

[total hours]+ IIF([total hours]=””,[total minutes : seconds],”:”+ [total minutes : seconds])

So just as I’m about to finish this set of calculations, a new message appears in my inbox. I subscribe to Tableau Zen Master – Jonathan Drummey’s blog, and he just posted a much simpler version of time duration on, Drawing with Numbers. I guess I just prefer the scenic route. Check it out his work though, it is great.

How have you handled time duration formatting in Tableau?


Share Button

Tableau Server Tip

Tip time again. And this one focuses on Tableau Server. One of my favorite features of Tableau Server is the ability to publish data sources. I work for a global company and we constantly share data with teams in international offices. This data can range from SQL Server/Oracle data to Excel files stored on a multitude of share drives. Publishing data sources allows our global teams to all access data needed for a given project or report, without needing access to where the underlying data is stored. This has been advantage and allowed collaboration between regions, all while working with a single version of the truth.

Ok, now getting to the tip. Let’s say you’re given a workbook that already contains charts, tables, dashboards, and of course, calculated fields. The workbook is connected to a published data source on your server. You try to edit a calculated field and the only option is Edit Copy, which makes a new calculated field.

ScreenHunter_02 Jul. 24 16.45

This is not ideal because the calculated field you’d like to edit is already embedded in several locations of the workbook. So how do you edit the original field?

Easy, simply right click on the data source name and select Create Local Copy.

ScreenHunter_03 Jul. 24 16.48

Save the data source to your desired folder. You’ll now have a new data source.

ScreenHunter_04 Jul. 24 16.50

Next, swap data sources so the exists workbook sources from the local copy.

Select Data > Replace Data Source…

ScreenHunter_01 Jul. 24 19.59

Now when you right click to edit a calculated field, you’ll be able to edit the original field that is already being used throughout the workbook.

ScreenHunter_05 Jul. 24 16.51

From here, you can proceed with the needed task. Just remember that if you republish the data source, your calculated field edits will be available for others. So depending on your ultimate goal, know if you should overwrite the original published data source or not.

Thanks! And happy Tableau’ing!

Share Button