I have a table of events with two columns, A: Start time & B: Label.
The start times can be both in the past(ongoing) and future (upcoming).
The table can contain rows with the same label appearing multiple times at different start times.
Labels will only appear as at most 1 ongoing row.
I want to colour the background of upcoming rows if that row's label appear on an ongoing row AND the start time of the upcoming row is within 10 minutes from current time.
I've tried SubscriptionFilter but since it can't be used to compare timestamps (VALUE=101<=[Sum:[DataminerTime],00:10:00]]) I'm stuck.
If I understand correctly, you are well on your way to a working solution, but the filter is not working?
DateTime fields are converted ToOADate when filtering. That means they are converted to a double.
Problem is I don't think there's a format string that can output this double from a datetime directly. You'd need to execute a script to execute the ToOADate method and then output the result.
Eventually though, I think you'll run into performance issues because DataMinerTime updates every second, so you'll be executing scripts and setting new subscriptionfilters every second...
To resolve this, you could:
- Use the [DataMinerTime] as a timer to execute a script on page level. Format the DateTime with "mm" for instance rather than seconds so it doesn't execute the script every second but rather every minute.
- Use the DateTime.Now + 10 min ToOADate in the script to output the current time in double.
- Use that output in your subscriptionfilter.