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…
- Use the DateTimeNow function to get today’s date.
- Strip off the time stamp.
- Attach a 6am timestamp instead.
- 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.
how can I setup a week from Friday to Friday?
Can you explain a little bit more? I get the general idea, but more specifically what result are you trying to arrive at…Friday as a 1 or 0?