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.
How would I go about solving the first bullet point, executing a script every minute in visual overview?
You use the Trigger=ValueChanged here and then the DataMinerTime placeholder like I described in the bullet point somewhere in your Parameters for instance. Even though you might not want to use it in your script, using it like this will trigger the script whenever the value of the formatted DataMinerTime changes.
If I understand correctly you want to color a row if the label is ongoing on ANY row AND is also closeby on ANY row? i.e. you want to figure out which labels are close together in terms of time?
How do you visualize your rows? With a children shape? What do you have so far? Feel free to edit your question.