Use Spotfire Date and Time Functions to Create a Dashboard

This week, I worked on a dashboard where the user requested a snapshot of yesterday’s operational activities. The dashboard itself was simple enough, a single page with four visualizations. Sounds easy, right? They wanted to see a snapshot of yesterday’s operations, which is not exactly the same as yesterday. Yesterday actually means from 6:00 am yesterday morning to 5:59 am of this morning. I needed to use Spotfire date and time functions. This small challenge took me long enough to figure out that I thought it warranted a blog post.

Data Limiting with Expressions

Since there were only 4 visualizations, I planned on using data limiting with expressions. I wanted to use a simple expression like this…to capture all the activites in the time log for “yesterday”.

[time log start date] >= [yesterday ops start] and [time log end date] <= [yesterday ops end]

Spotfire Date and Time Functions

Spotfire has many useful date and time functions. Out of the gate, I planned to…

1. Use the DateTimeNow function to get today’s date.
2. Strip off the time stamp.
3. Attach a 6am timestamp instead.
4. Use DateAdd to subtract a day off of that.

I knew how to complete steps 1 and 4. Steps 2 and 3 would take a bit of trial and error. Here is the final working expression.

DateAdd("dd",-1,DateTime(year(DateTimeNow()),month(DateTimeNow()),Day(DateTimeNow()),6,0,0,0))

Do who to the what now? Let’s break it down.

Breaking Down the Expression

I combined the following functions to make it work.

• DateTimeNow – gets the current date and time.
• Year – extracts the year from the current date and time.
• Month – extracts the month from the current date and time.
• Day – extracts the day from the current date and time.
• DateTime – I use the DateTime function to string together a date using the year, month, and day from DateTimeNow. Then, I added on my own timestamp using “6,0,0,0”.
• DateAdd – subtracts a day off the final result to get yesterday’s operational start.

I had a little bit of trouble with the DateTime function at first. I used the help function to get an example calculation. It wasn’t sure if all arguments were needed. They were. I had to enter all 7 arguments to get it to work, but it did exactly what I needed.

This was a nice refresher in how to use Spotfire date and time functions. Next week, I’m switching gears to talk about HTML & CSS.

Spotfire Version

Content created with Spotfire 10.2.

If You Enjoyed This Check-Out…

• How to Use Over Statements
• How to Use Node Navigation
• Calculations with Axis Names