Dashboards & Visualizations

How to change cell color based on relative date?

mistydennis
Communicator

I would like to change the color of a cell based on the date. If the date is in the future, then green. If the date is today, then yellow. If the date is in the past, then red. I have read many posts about how to do this based on _time and appending a word to the date ("overdue", for example), but I can't seem to make this work in my case. The field I'm working with is dueDate (not based on _time), and it's formatted as %Y-%m-%d. 

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

strftime() converts an epoch time to a string.

strptime() parses a string to an epoch time.

Both take a time format string

Date and time format variables - Splunk Documentation

You can easily compare epoch times since they are just numbers

| eval stringdate=if(epochdate=relative_time(now(),"@d"),mvappend(stringdate,"today"), if(epochdate<relative_time(now(),"@d"),mvappend(stringdate,"before today"),mvappend(stringdate,"after todate")))

View solution in original post

0 Karma

mistydennis
Communicator

Yes, newduedate is now in epoch format. How do I now get it to:

  1. Be in %Y-%m-%d format
  2. Adjust the relative time so it's accounting for future dates (any date after today), today, and any date prior to today
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

strftime() converts an epoch time to a string.

strptime() parses a string to an epoch time.

Both take a time format string

Date and time format variables - Splunk Documentation

You can easily compare epoch times since they are just numbers

| eval stringdate=if(epochdate=relative_time(now(),"@d"),mvappend(stringdate,"today"), if(epochdate<relative_time(now(),"@d"),mvappend(stringdate,"before today"),mvappend(stringdate,"after todate")))
0 Karma

mistydennis
Communicator

Thank you, that got me close enough!

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Make the dueDate field a multi-value field with the second value being the colour you want the cell coloured, then use CSS to hide the second value.

Have a look at this solution and see if you can adapt it to your situation

Solved: Re: Highlight row if unique values exist within dy... - Splunk Community

0 Karma

mistydennis
Communicator

Yes, that is what I'm looking for. But I'm having trouble as well formatting my query correctly - I'm not sure what the correct syntax is. 

I read another post here which you answered (🙂) and said to try this:

| eval newtime=if(relative_time(_time,"@d")=relative_time(now(),"@d"),mvappend(strftime(_time,"%Y-%m-%d"),"today"), if(relative_time(_time,"@d")=relative_time(now(),"-7d@d"),mvappend(strftime(_time,"%Y-%m-%d"),"overdue"),strftime(_time,"%Y-%m-%d")))

 

But I'm not sure how to adjust this for my case - I tried plugging in dueDate instead of _time, it did not work. And I don't know how to format the strf and strptime correctly so that I am going beyond the past 7 days. 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Is dueDate a string or an epoch time?

0 Karma

mistydennis
Communicator

It is a string.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

You should convert it an epoch time with strptime() so you can compare with the time returned by relative_time()

0 Karma

mistydennis
Communicator

Apologies, but dealing with timestamps has always been confusing for me. So still working with one of your answers from another post, I have:

 

| eval newduedate=strptime(dueDate, "%Y-%m-%d")
| eval newduedate=if(relative_time(dueDate,"@d")=relative_time(now(),"@d"),mvappend(strftime(dueDate,"%Y-%m-%d"),"today"),
if(relative_time(dueDate,"@d")=relative_time(now(),"-7d@d"),mvappend(strftime(dueDate,"%Y-%m-%d"),"overdue"),strftime(dueDate,"%Y-%m-%d")))

 

 

I understand I'm converting to epoch time with the first line, but after that I get lost. And this produces no results, so I'm clearly doing something wrong.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Try something like this

| eval newduedate=strptime(dueDate, "%Y-%m-%d")
| eval dueDate=if(newduedate=relative_time(now(),"@d"),mvappend(dueDate,"today"),
if(newduedate=relative_time(now(),"-7d@d"),mvappend(dueDate,"overdue"),dueDate))

newduedate is the epoch version of dueDate (which remains a string)

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...