How to assess datetime column and replace if outside desired parameter

[Brendan Civ]

I have a series of data that includes datetime columns for the start and end of processes in the format "DD/MM/YYYY HH:MM:SS A/PM", e.g. "28/05/2020 03:07:10 AM".

I need to extract all the relevant data for different time periods, e.g. daily from 06:00 AM to 05:59 AM, however if the start or end timestamp is outside of my desired period i miss theportion of data that is in the period. I've expanded the parameters to include any data that crosses the borders of the time period, but i only want to include thedata from these events that occurred during the period not before or after it.

In excel i've created and adjusted time column=IF(ISBLANK('Start Timestamp Cell'), 'Reference Start Timestamp Cell', (IF('Start Timestamp Cell'

[David Boot-Olazabal]

Hi Brendan,

You can use a document property for the reference timestamp. You can set this to the actual time of the load data process if you like and have it triggered each time you open the dashboard. You can find some references to this on our community and/or on the spotfired.blogspot.com site.

You can use R code to update the document property, each time the dashboards opens. Or you can set it via an iron python script to a fixed timestamp (end of current day for exampe).

You can find an example here:https://community.spotfire.com/questions/set-document-property-default-current-date

Kind regards,

David

[Brendan Civ]

Hi David,

 

Thanks for the feedback and suggestion. I'll give that a try as Ive currently gotten it working with what can only be described as the worst hodgepodge of calculated columns possible haha.

 

Brendan