Dashboards That Pop: Dynamic Date 'Alerts'
I am back with another Dashboards That Pop entry. You can find the rest of the series here. This time we’re looking at how you can create visual cues when a date is approaching or has passed. This can help you monitor due dates or stay on track for upcoming events.
Here I have a basic dashboard showing events and their event dates. There are all sorts of changes happening based on if an event date is occurring within the next 7 days or not. I wouldn’t incorporate all of these different formatting options on a single dashboard, but I wanted to show you all a buffet of options you can play with. I also added a filter so you can see what the indicators look like when there are not any events occurring within the next 7 days.
In this walkthrough I have arbitrarily picked 7 days to be the timeframe I care about, but you can pick any number of days or even change up the units of measure (seconds, hours, months, or years).
Indicator
The indicator set-up will be a bit different than the Table and List elements, so I’ll start with that one first.
First, I added my data layer and then added a filter to only use records where the event data is within the next 7 days.
Next, I toggled on the Reference option. I used a Fixed value and set that to 1.
Moving on to the Indicator tab, I removed the {reference} value from the bottom row and toggled on Conditional Formatting.
Now I can individually style the text, text color, icons, and icon color for values at and above my reference value (which was 1).
To get the background color to change, I enabled Advanced Formatting.
I will need to use some Arcade to get the background color to change. If you are not familiar with using Advanced Formatting and Arcade in Dashboards, you might want to check out Dashboards That Pop: Arcade.
If you are not comfortable with the Advanced Formatting, you can cheat a bit by setting up your conditional formatting (text, text color, icon, and icon color) before enabling Advanced Formatting. Just keep in mind what your background color will be and ensure your text colors will be legible once it is applied. All of those settings will carry over when you go into Advanced Formatting mode, so you simply need to add your background color in the return statement. The first return statement is the at and above value, while the second one is the below value.
List & Table Element
Both elements require Advanced Formatting, so the general process is the same. Again, I encourage you to check out Dashboards That Pop: Arcade, if you are new to Advanced Formatting.
First, I used Arcade to determine how many days there are between Now and the Event Date.
var edate = DateDiff($datapoint.EventDate, Now(), 'Days')
Now that I have the number of days between those two points of time, I can use that value in a simple When statement to do all sorts of things. The statement basically says...
When the days between now and the event are equal to or less than 7, provide me something, if not provide me something else.
What makes this useful is that something provided can be a color, text, icon name, or emoji.
var eemoji = When(edate <= 7, ':warning:', '')
var ecolor = When(edate <= 7, '#70161E', '')
var etextcolor = When(edate <= 7, '#FFFFFF', '')
Next, I plugged in my new variables into the return statement.
Bonus – Multiple Date ‘Alerts’
What if you want to set up multiple time periods to alert on? Easy, you just need to expand your When statement.
I have different colors displaying based on when the event occurs:
- Event Date has passed: Red ⯀
- Event is today: Bright Orange ⯀
- Event is less than 3 days away: Light Orange ⯀
- Event is over 3 days away but less than 7: Yellow ⯀
var ecolor = When(edate < 0, '#CC2836',
edate >= 0 && edate < 1, '#F95738',
edate >= 1 && edate < 3, '#EE964B',
edate >= 3 && edate <= 7, '#F4D35E','')