Is it possible to use dynamic dates in conditional formatting?

[Mikael Helldén]

In a chart, I want to use conditional formatting to color the background if the date is before e.g. today's date. I that possible? I've tried different versions of {TODAY}, but it doesn't work.

Hard coding the date (e.g. 2024-06-15) works, but I obviously don't want to change the value every time I look at the chart.

[Shashwat Khare · Accepted solution]

Hello @Mikael Helldén ,

This is Shashwat from Atlassian Analytics and here to help! :) 

Unfortunately, applying conditional formatting on relative date variables isn't yet supported, however, we have an existing feature request for the same as below:
https://jira.atlassian.com/browse/ANALYTICS-219

I have personally watched the FR and added you, so we help gain more traction on its implementation and also so we receive notifications on it moving forward.

I would recommend that you upvote the same so that you help add more traction to its implementation and so you receive updates in the future as well.


Best,
Shashwat

[Mikael Helldén]

Thank you for your reply, @Shashwat Khare! I've voted for the FR. :)

[Tommaso Gionfriddo _Adeptask_]

Hi @Mikael Helldén

This is a problem I've been tackling for some time. Whilst I have technically been able to solve it, you may not love the solution.

Essentially, if you rename each column to something static ( I like to use an invisible character such as U+200E, with one additional character added for each additional column) you can then apply the conditional formatting to the columns, regardless of what date they are.

Next, for each of these static column titles, add a new text chart over the top, and have the date for that column display in the text chart.

Whilst it can look a tad janky, it's certainly passable in many situations.

I don't have access to any Analytics applications currently, sorry, otherwise I'd provide some visuals. If you would like a more detailed explanation, please let me know and I can see if I can draw some diagrams or get access to an analytics license from someone.

[Mikael Helldén]

Hi  @Tommaso Gionfriddo _Adeptask_ ,
Thank you for your input! Will look into your proposed solution while we're waiting for the feature request to be implemented.
Cheers!

[Tommaso Gionfriddo _Adeptask_]

Awesome! If you get something working, I'd love to see your approach and if it differed at all from mine.

Unfortunately I don't have any of the code snippets from when I did this previously. But I recall finding the explode function incredibly helpful.

I'll try find the code snippets and provide them here if I'm able to get my hands on them. Or once I write them again myself.