Date Diff beast mode calculation with multiple variables

lowrydomo

Hi, I am trying to write a beast mode that provides the number of days between when a file was received and when it was completed, if the file was received after the expiration date then date received is used to calculate the number of days, if the file was received before the expiration date then the expiration date is used to calculate the number of days. The logic on this was pretty simple, but I have run into an issue when another variable comes into play, occasionally we will have a file that has an expiration date after the date completed so for example, a file will have a date received of 1/2/2020 a date completed of 1/15/2020 and then an expiration date of 1/5/2021, so with the logic that is currently set up on the beast mode it will use the number of days between the expiration date and date completed because the expiration date is after the date received, the problem with this is that in the case where the expiration date is after the date completed I receive a negative day count which throws off all of my averages. So in the example I provided the number of days should be 13, but instead it is -355.

The logic that needs to be added basically needs to say if the expiration date is after the date completed then use date received instead of expiration date. Below is the logic that I am already using, which is working correctly, I just need that last piece added and I am having some trouble.

avg(DATEDIFF(`DateCompleted`,case when datediff(`Date Received`,`Expiration Date`) > 0 then `Date Received` else `Expiration Date` end))

lowrydomo

Never-mind I figured this out.

lowrydomo

Never-mind I figured this out.

jaeW_at_Onyx

@lowrydomo

depending on your data COALESCE / IFNULL could work, but most likely a simple CASE statement would be an easy way forward.