How to compare dates for expiration purposes?

[albinortiz]

Greetings,
I am trying to create a panel that helps me track expired trainings. What I am trying to do is to take the Class_Date and add 1year to it then compare that to todays date. So, IF Class_Date + 1 year > todays date, "Expired", "Valid".

This is what I have so far:

| inputlookup Classes.csv
| eval timenow = strftime(now(), "%Y-%m-%d")
| eval ConvertedClassDate = strptime(Class_Date, "%Y-%m-%d")
| eval ConvertedClassDate = ConvertedClassDate + 31536000)
| eval ConvertedTodaysDate = strptime(timenow, "%Y-%m-%d")
| eval Status = IF((ConvertedTodays_Date < ConvertedClassDate), "Expired", "Valid")
| table Username, Class_Date, Status

All help is greatly appreciated!

[micahkemp]

I'm not sure where the above effort stood, but this seems to work, and is based from the same makeresults set:

| makeresults 
| eval Class_Date="2018-1-1" 
| append [| makeresults | eval Class_Date="2018-12-12"] 
| append [| makeresults | eval Class_Date="2017-1-2"] 
| append [| makeresults | eval Class_Date="2017-1-3"] 
| eval class_plus_one_year = relative_time(strptime(Class_Date, "%Y-%m-%d"), "+1y")
| eval Status = if(now() > class_plus_one_year, "Expired", "Valid")

[493669 · Accepted solution]

now() is already in epoch so no need to convert...simply you can compare like

| eval Status = IF((now() < ConvertedClassDate), "Expired", "Valid")

so you can try this:

| inputlookup Classes.csv
| eval ConvertedClassDate = strptime(Class_Date, "%Y-%m-%d")
| eval ConvertedClassDate = ConvertedClassDate + 31536000)
| eval Status = IF((now() < ConvertedClassDate), "Expired", "Valid")
| table Username, Class_Date, Status

Here Class_Date is expected in Year-month-day format like 2018-02-15

[micahkemp]

You may consider using relative_time instead of adding a year's worth of seconds:

 | eval ConvertedClassDate = relative_time(ConvertedClassDate, "+1y")

[albinortiz]

That might be a good option too!

EDIT:

Changed it to that and it also works.

Thanks!

[albinortiz]

Hello,

I am almost there. I can see the valid and expired ones. However, this is happening:

username1 2018-1-1 Valid
username3 2018-12-12 Valid
username4 2017-1-2 Expired
username5 2017-1-3 Expired

The first entry should be expired since today's date is 2018-02-15. Why is the code making the comparison using the year?

[493669]

When I tried I get exact reverse output ...check what sign you are using > or < in eval expression:
Try this run anywhere search:

|makeresults|eval Class_Date="2018-1-1"
|append[|makeresults|eval Class_Date="2018-12-12"]
|append[|makeresults|eval Class_Date="2017-1-2"]
|append[|makeresults|eval Class_Date="2017-1-3"]
| eval ConvertedClassDate = strptime(Class_Date, "%Y-%m-%d")
 | eval ConvertedClassDate = ConvertedClassDate + 31536000 |eval current=now()| eval Status = IF((now() < ConvertedClassDate), "Expired", "Valid")

[albinortiz]

The issue I see is that 2018-12-12 is not expired and it is marking it as expired. If I change the comparison character, it will say 2018-01-01 is valid when in reality it is not. I feel like it is making the comparison using the year and not the whole date.

Username ClassDate Status

---

username1 2018-1-1 Valid
username2 2018-1-1 Valid

These two lines should be Expired.

I tried yours and it also does the same thing.

[493669]

If I tried for date 2018-1-1 it is coming as expired only as expected ...Not sure why are receiving it as valid...could you please provide your query and also try below query :

|makeresults|eval Class_Date="2018-1-1"
| eval ConvertedClassDate = strptime(Class_Date, "%Y-%m-%d")
  | eval ConvertedClassDate = ConvertedClassDate + 31536000 |eval current=now()| eval Status = IF((now() < ConvertedClassDate), "Expired", "Valid")

[albinortiz]

Here's my code:
| inputlookup Training.csv
| eval ConvertedClassDate = strptime(ClassDate, "%Y-%m-%d")
| eval ConvertedClassDate = (ConvertedClassDate + 31536000)
| eval Status = IF((now() < ConvertedClassDate), "Expired", "Valid")
| table Username, ClassDate, Status

I changed the date your code so you would see what is going on:

|makeresults|eval Class_Date="2018-12-12"
| eval ConvertedClassDate = strptime(Class_Date, "%Y-%m-%d")
| eval ConvertedClassDate = ConvertedClassDate + 31536000 |eval current=now()| eval Status = IF((now() < ConvertedClassDate), "Expired", "Valid")

[493669]

here logic is that :
if (Classdate + 1year ) is less than today's date then it is expired else Valid...
if it is correct logic then
for classdate=2018-12-12 +1year=2019-12-12 is not less than todays date so it will be Valid....right?

[albinortiz]

Wow. I guess I need to take a break. You have been right the whole time. I will drink a few beers on your name today.

[493669]

🙂 ha ha ..

[493669]

try if(now() > class_plus_one_year, "Expired", "Valid")