topic Re: If a date is NOT in the current month in App Development

If a date is NOT in the current month

Sam_Thomas — Thu, 27 Apr 2023 14:54:36 GMT

I currently have an expression that checks whether a date (Stat_Date) occurred within the last X number of days (based on the Frequency field.

I need to add into an expression criteria that asks the question 'is the Stat_Date' later than the last day of the previous month.

My current expression:

num(if(date(today()>([Stat_Date]+[SCHEDULED])), '1', '0')) as [Flag]

I need to it to also check whether the date is not in the current month.

Thanks

Re: If a date is NOT in the current month

MarcoWedel — Thu, 27 Apr 2023 17:20:49 GMT

maybe like this?

not InMonth(YourDateToCheck,Today(),0)

Re: If a date is NOT in the current month

Chanty4u — Thu, 27 Apr 2023 17:34:13 GMT

Try this

num(if(

monthstart(today()) = monthstart([Stat_Date]) and date(today()) > ([Stat_Date] + [SCHEDULED]),

'1',

monthstart(today()) < monthstart([Stat_Date])

), '0')) as [Flag]

Re: If a date is NOT in the current month

Sam_Thomas — Fri, 28 Apr 2023 09:27:00 GMT

Thanks. Even though I was a little incorrect with my own question, this still enabled me to get to the correct answer. However, I misunderstood the business definition. I wonder if you may be able to assist me in tweaking the expression further.

In plain language, I want to:

Return a 1 if today's date is greater than the last stat_date plus the scheduled frequency days.

I.e. if the last check was 10.04.2022 and the scheduled frequency is 365 days, then it's out of date and should return a 1.

- However, there is a lead time as to when we receive updates on new status checks (stat_date). As a rule, the business don't want to include any checks that should have been done in the current calendar month, as they may have been completed but not yet recieved.

For example, the above stat_date for 10.04.2022 should be classed as Out of Date (1), but, as the check may have been completed this month, we may not have had the results yet. As such, I want to class all 'out of date' checks as 'In Date', as the check could have been completed recently - we just haven't had the results. I hope that makes sense. Any help would be gratefully received.

edit: in plain language:

if today's date is greater than the stat date plus the scheduled frequency, and that calculated date not occurring in the current month, return a 1. Else, return a 0.

Re: If a date is NOT in the current month

Sam_Thomas — Fri, 28 Apr 2023 15:41:31 GMT

Solved

num( if( date(today()>("Stat Date"+"SCHEDULED FREQUENCY"))
AND (month(today()) <> Month("Stat Date"+"SCHEDULED FREQUENCY")) , 1, 0)) AS [OOD Flag],

Re: If a date is NOT in the current month

MarcoWedel — Fri, 28 Apr 2023 20:19:36 GMT

Nice to hear your issue is solved.

If your expression is working for you, then maybe this one might also:

-(MonthStart(Today())>[Stat Date]+[SCHEDULED FREQUENCY]) as [OOD Flag]

(If "Stat Date"+"SCHEDULED FREQUENCY" has to be less than Today() and must not be in the current month then it has to be in an earlier month, i.e. before the start of the current month, right?
Also using Month() instead of MonthName() in your expression might lead to unexpected results for same months in different years)

hope this helps

Marco