Beast Mode to Compare Dates with IF

PJG · 2023-03-29T18:03:32+00:00

There was an error rendering this rich post.

Hi again,

This might be my last piece to finalizing my current dashboard.

Each project has three types of go-live dates:

Initial
Adjusted
Actual

For each of these three types of go-live dates, there can be 10 instances, so 30 date attributes in all. They're numbered Initial 1, Adjusted 1, Actual 1, Initial 2, Adjusted 2, Actual 2, etc.

The first thing I want to do is exclude any instances where there's an Actual date. For instance, if there's an Actual 4 Date already, then I don't want to include the Initial 4 or Adjusted 4 in the following criteria. I have the following criteria to determine if a project is on time or not

If Initial is blank = On-Time
If Adjusted </= 25 days after Initial (or earlier than Initial) = On-Time. > 25 days = Overdue
If no Adjusted, & Initial > 25 days in the past = Overdue
If Adjusted > 25 days in the past = Overdue

I can get as far as something like this to compare the two dates, but fitting all these pieces together is not working for me.

when Adjusted 1 <= DATE_ADD(Initial 1, interval 25 day) then 'On-Time'

Any help would be greated appreciated!

Find more posts tagged with

Accepted answers

GrantSmith

CASE WHEN `Actual 1` IS NULL THEN
  —- If Initial is blank = On-Time
  CASE WHEN `Initial 1` IS NULL THEN 'On-Time'
  —- If Adjusted <= 25 days after Initial (or earlier than Initial) = On-Time.
  WHEN `Adjusted 1` <= DATE_ADD(`Initial 1`, interval 25 day) then 'On-Time'
  —- > 25 days = Overdue
  WHEN `Adjusted 1` > DATE_SUB(`Initial 1`, interval 25 day) then 'Overdue'
  —- If no Adjusted, & Initial > 25 days in the past = Overdue
  WHEN Adjusted 1` IS NULL AND `Initial 1` > DATE_SUB(CURDATE(), interval 25 day) then 'Overdue'
  —- If Adjusted > 25 days in the past = Overdue  
  WHEN Adjusted 1` > DATE_SUB(CURDATE(), interval 25 day) then 'Overdue'
  ELSE 'Unknown'
  END
ELSE
  'Has Actual'
END

You can have multiple WHEN clauses as part of your CASE statement and even nest case statements. The case statement will early terminate the evaluation as soon as it finds a true condition.

This is just for your date 1 field but feel free to tweak it as needed.

All comments

GrantSmith

CASE WHEN `Actual 1` IS NULL THEN
  —- If Initial is blank = On-Time
  CASE WHEN `Initial 1` IS NULL THEN 'On-Time'
  —- If Adjusted <= 25 days after Initial (or earlier than Initial) = On-Time.
  WHEN `Adjusted 1` <= DATE_ADD(`Initial 1`, interval 25 day) then 'On-Time'
  —- > 25 days = Overdue
  WHEN `Adjusted 1` > DATE_SUB(`Initial 1`, interval 25 day) then 'Overdue'
  —- If no Adjusted, & Initial > 25 days in the past = Overdue
  WHEN Adjusted 1` IS NULL AND `Initial 1` > DATE_SUB(CURDATE(), interval 25 day) then 'Overdue'
  —- If Adjusted > 25 days in the past = Overdue  
  WHEN Adjusted 1` > DATE_SUB(CURDATE(), interval 25 day) then 'Overdue'
  ELSE 'Unknown'
  END
ELSE
  'Has Actual'
END

You can have multiple WHEN clauses as part of your CASE statement and even nest case statements. The case statement will early terminate the evaluation as soon as it finds a true condition.

This is just for your date 1 field but feel free to tweak it as needed.

PJG

Hi Grant,

Thank you so much!

First, I should have expressed how I am completely new to Beast Mode, and my coding experience has been almost zero since I graduated college 20+ years ago. I suspect you wanted me to convert a few of these lines into more codey things, but that might be beyond me :)

I apologize for the stupid things I'm inevitably going to say :)

I renamed my example attributes to my actual attributes (which I should have done to start with), and tweaked a few things, I thought correctly (but probably not). However, I am getting a syntax error on this, and I wish there was a Step through option like Excel to see exactly where. Apologies if I made it worse instead of better.

What does black text mean? It seems it means it's not recognized by BM, so is it still ok to have there, and it's just treated as comments?

Below is the code, pasted here, but because this forum changes a lot of it, also including a screenshot.

Any further help to get this working would be appreciated greatly.

CASE WHEN Actual Business Go-Live 1 Date_p IS NULL THEN

—- If Initial Business Go-Live 1 Date_p IS NULL = 'On-Time'

CASE WHEN Initial Business Go-Live 1 Date_p IS NULL THEN 'On-Time'

—- If Adjusted Business Go-Live 1 Date_p <= 25 days after Initial Business Go-Live 1 Date_p (or earlier than Initial Business Go-Live 1 Date_p) = 'On-Time'

WHEN Adjusted Business Go-Live 1 Date_p <= DATE_ADD(Initial Business Go-Live 1 Date_p, interval 25 day) then 'On-Time'

—- > 25 days = 'Overdue'

WHEN Adjusted Business Go-Live 1 Date_p > DATE_SUB(Initial Business Go-Live 1 Date_p, interval 25 day) then 'Overdue'

—- If no Adjusted Business Go-Live 1 Date_p AND Initial Business Go-Live 1 Date_p > 25 days in the past = 'Overdue'

WHEN Adjusted Business Go-Live 1 Date_p IS NULL AND Initial Business Go-Live 1 Date_p > DATE_SUB(CURDATE(), interval 25 day) then 'Overdue'

—- If Adjusted Business Go-Live 1 Date_p > 25 days in the past = 'Overdue'WHEN Adjusted Business Go-Live 1 Date_p > DATE_SUB(CURDATE(), interval 25 day) then 'Overdue'

ELSE 'Unknown'

END

ELSE

'Has Actual'

END

https://i.imgur.com/ukb7kHr.jpg

GrantSmith

That's a great first attempt. What you're missing is surrounding your field names with backticks (`) to show that they are fields in your dataset and not actual beast mode code syntax.

CASE WHEN `Actual Business Go-Live 1 Date_p` IS NULL THEN
—- If Initial Business Go-Live 1 Date_p IS NULL = 'On-Time'
CASE WHEN `Initial Business Go-Live 1 Date_p` IS NULL THEN 'On-Time'
—- If Adjusted Business Go-Live 1 Date_p <= 25 days after Initial Business Go-Live 1 Date_p (or earlier than Initial Business Go-Live 1 Date_p) = 'On-Time'
WHEN `Adjusted Business Go-Live 1 Date_p` <= DATE_ADD(`Initial Business Go-Live 1 Date_p`, interval 25 day) then 'On-Time'
—- > 25 days = 'Overdue'
WHEN `Adjusted Business Go-Live 1 Date_p` > DATE_SUB(`Initial Business Go-Live 1 Date_p`, interval 25 day) then 'Overdue'
—- If no Adjusted Business Go-Live 1 Date_p AND Initial Business Go-Live 1 Date_p > 25 days in the past = 'Overdue'
WHEN `Adjusted Business Go-Live 1 Date_p` IS NULL AND `Initial Business Go-Live 1 Date_p` > DATE_SUB(CURDATE(), interval 25 day) then 'Overdue'
—- If Adjusted Business Go-Live 1 Date_p > 25 days in the past = 'Overdue'
WHEN `Adjusted Business Go-Live 1 Date_p` > DATE_SUB(CURDATE(), interval 25 day) then 'Overdue'
ELSE 'Unknown'
END
ELSE
'Has Actual'
END

PJG

Hi Grant,

Backticks just don't paste into the forum for me, but I think I have those per screenshot?

Though I am a bit confused, your lines 2, 4, 8, 10 don't have backticks either, while others do?

GrantSmith

I missed that in your screenshot but you did have the backticks. The forums interpreted it as a code block when it was pasted in.

2,4,8 and 10 are comments where you can add additional information for context but won't be evaluated, that's what the two dashes represent as part of the beast mode.

What I'll do when diagnosing a beast mode its I'll comment out the lines in the beast mode and then one by one remove the comments to see where the error is occurring.

PJG

Hi Grant,

Happy Monday to you. Back on this today.

Understand the commenting method now. Silly me.

EDIT: Deleted this, as I think I may be making progress. Further update soon….

GrantSmith

Can you provide a sample CSV or excel file (with just the dates with their field names, and no other identifying information)?

PJG

Hi Grant,

Sorry, you were too fast; I got it working and added an edit above. I'm not exactly sure what the problem was, but I will call it user error :) I tried to strip out the comments too, so this is what got it running for me:

CASE WHEN `Actual Business Go-Live 1 Date_p` IS NULL THEN

CASE WHEN `Initial Business Go-Live 1 Date_p` IS NULL THEN 'On-Time'

WHEN `Adjusted Business Go-Live 1 Date_p` <= DATE_ADD(`Initial Business Go-Live 1 Date_p`, interval 25 day) then 'On-Time'

WHEN `Adjusted Business Go-Live 1 Date_p` > DATE_SUB(`Initial Business Go-Live 1 Date_p`, interval 25 day) then 'Overdue'

WHEN `Adjusted Business Go-Live 1 Date_p` IS NULL AND `Initial Business Go-Live 1 Date_p` > DATE_SUB(CURDATE(), interval 25 day) then 'Overdue'

WHEN `Adjusted Business Go-Live 1 Date_p` > DATE_SUB(CURDATE(), interval 25 day) then 'Overdue'

ELSE 'Unknown'

END

ELSE

'Has Actual'

END

I'm not quite making sense of the results though. I've validated the data. The results are below for correct values vs what is displayed:

Has Actual | 27 | 27 | Correct
On-Time | 76 | 32 | Incorrect
Overdue | 11 | 45 | Incorrect
Unknown | 0 | 10 | Incorrect

The good news is that the totals are the same, 114 for both.

I will take you up on your offer and attach a .csv file. Can't attach directly, so hopefully this link works: https://drive.google.com/file/d/1CWjOmJJZgBQHQcDuFJG_Ishhh5ixuoWp/view?usp=sharing

Thank you!

EDIT: I just added an extra column to the csv file to show what the status should be based on criteria.

PJG

Hi Grant,

Stared at this for a while.

If more than one line is true, is it causing issues? For instance, these can both be true:

The adjusted can be </= to 25 days after Initial (On-Time)

The adjusted can be > 25 days after today (Overdue)

Thanks

GrantSmith

The way a case statement would work is that it will evaluate the first condition it finds to be true and then ignores all other condition. So in the case of how the logic is written, it would return 'On-Time' as that's what comes first.

PJG

Understood.

Any other ideas? Have a chance to look at the file?

Is there a quick way to look at the specific projects/dates it's identifying as Unknown, Overdue, etc.

And again, thank you so much for your help. Massively appreciated.

PJG

I think I see the problem, ignore me for a minute :)

PJG

Haven't quite got the numbers adding up yet, but I managed to get rid of the Unknown by adding an extra line:

WHEN `Adjusted Business Go-Live 1 Date_p` IS NULL AND `Initial Business Go-Live 1 Date_p` <= DATE_SUB(CURDATE(), interval 25 day) then 'On-Time'

PJG

I got this working!!! Oh my gosh, what a joy and relief! :)

I learned so many things, including how to properly test a line at a time. Turns out that my problems with the initial code that you shared were actually the commented rows. It requires two dashes, but this forum turned one of your dashes into something that wasn't recognized.

Ultimately, I think I had some less than or greater than symbols backward, and needed to switch some DATE_SUB to DATE_ADD….. or….. I'm not saying I "needed" to - maybe I'm doing things backward, but it's working at least! All lines validated and matching source data.

Here's the final code I'm using

CASE WHEN `Actual Business Go-Live 1 Date_p` IS NULL THEN

CASE WHEN `Initial Business Go-Live 1 Date_p` IS NULL THEN 'On-Time'

WHEN `Adjusted Business Go-Live 1 Date_p` IS NULL AND `Initial Business Go-Live 1 Date_p` <= DATE_SUB(CURDATE(), interval 25 day) then 'Overdue'

WHEN `Adjusted Business Go-Live 1 Date_p` IS NULL AND `Initial Business Go-Live 1 Date_p` > DATE_SUB(CURDATE(), interval 25 day) then 'On-Time'

WHEN `Adjusted Business Go-Live 1 Date_p` > DATE_ADD(`Initial Business Go-Live 1 Date_p`, interval 25 day) then 'Overdue'

WHEN `Adjusted Business Go-Live 1 Date_p` <= DATE_ADD(`Initial Business Go-Live 1 Date_p`, interval 25 day) AND `Initial Business Go-Live 1 Date_p` <= DATE_SUB(CURDATE(), interval 25 day) then 'Overdue'

WHEN `Adjusted Business Go-Live 1 Date_p` <= DATE_ADD(`Initial Business Go-Live 1 Date_p`, interval 25 day) AND `Initial Business Go-Live 1 Date_p` > DATE_SUB(CURDATE(), interval 25 day) then 'On-Time'

ELSE 'Unknown'

END

ELSE

'Has Actual'

END

If you think anything looks wrong with that, feel free to let me know.

I do have one final question that I thought would be simple, but is not working the way I imagined it. I have 10 instances of this, i.e. go-live date, 2, 3, 4, etc. I thought I could just copy/paste this 9 times, and update the attribute references, but that's giving me an error. I tried wrapping it with its own CASE / END, but still an error. How do I include all the other blocks of code within this Beast Mode?

Thank you so much for getting me started on this, and your patience and help.