topic Re: If statement with date in Qlik Answers

If statement with date

Jacktheripperz — Thu, 03 Apr 2025 10:26:20 GMT

Hi I have a text box that contains the following if statement

=if (DATE(TODAY() - MOD(WEEKDAY(TODAY()) + 2, 7) - 1, 'DD/MM/YYYY') = DATE(MAX(FLOOR([JOB_DATE])), 'DD/MM/YYYY'), '', 'ALERT *Missing Fridays Data for jobs')

which was supposed to warn for missing data on a friday. but this only works the following day because the max wont be that day so i tried to do it with a count

=IF(Count({<JOB_DATE = {"$(=DATE(TODAY() - MOD(WEEKDAY(TODAY()) + 2, 7) - 1))"}>} JOB_DATE) > 0, '', 'ALERT *Missing Fridays Data for jobs')

this didnt work because JOB_DATE contains date and time.
but floor doesnt work in the count so this doesnt work at all 😞

=IF(Count({<Date(Floor(JOB_DATE), 'DD/MM/YYYY') = {"$(=DATE(TODAY() - MOD(WEEKDAY(TODAY()) + 2, 7) - 1))"}>} JOB_DATE) > 0, '', 'ALERT *Missing Fridays Data for jobs')

does anyone have an idea how to fix this? seems simple but i have run out of solutions

Re: If statement with date

Or — Thu, 03 Apr 2025 10:53:48 GMT

Max() takes a second, optional parameter for rank. You could use Max(Field,2) to get the second highest value.

I'm also kind of confused about all your assorted date functions which seem cumbersome and unnecessary, but that's not really related to the problem, I guess.

DayName() will return the date without the time aspect (as will Floor()), but the former will treat the field as a date and the latter as a number)

Re: If statement with date

Jacktheripperz — Thu, 03 Apr 2025 11:35:51 GMT

Hi thanks for the reply.

the multiple functions were attempts to get the date I suppose I could make it really cumbersome and have 7

nested If statements along the lines of

=IF(DATE(TODAY() - MOD(WEEKDAY(TODAY()) + 2, 7) - 1, 'DD/MM/YYYY') = DATE(MAX(FLOOR([JOB_DATE])), 'DD/MM/YYYY'), '', 
IF(DATE(TODAY() - MOD(WEEKDAY(TODAY()) + 2, 7) - 1, 'DD/MM/YYYY') = DATE(MAX(FLOOR([JOB_DATE]), 2), 'DD/MM/YYYY'), '', 
IF(DATE(TODAY() - MOD(WEEKDAY(TODAY()) + 2, 7) - 1, 'DD/MM/YYYY') = DATE(MAX(FLOOR([JOB_DATE]), 3), 'DD/MM/YYYY'), '', 
IF(DATE(TODAY() - MOD(WEEKDAY(TODAY()) + 2, 7) - 1, 'DD/MM/YYYY') = DATE(MAX(FLOOR([JOB_DATE]), 4), 'DD/MM/YYYY'), '', 
IF(DATE(TODAY() - MOD(WEEKDAY(TODAY()) + 2, 7) - 1, 'DD/MM/YYYY') = DATE(MAX(FLOOR([JOB_DATE]), 5), 'DD/MM/YYYY'), '', 
IF(DATE(TODAY() - MOD(WEEKDAY(TODAY()) + 2, 7) - 1, 'DD/MM/YYYY') = DATE(MAX(FLOOR([JOB_DATE]), 6), 'DD/MM/YYYY'), '', 
IF(DATE(TODAY() - MOD(WEEKDAY(TODAY()) + 2, 7) - 1, 'DD/MM/YYYY') = DATE(MAX(FLOOR([JOB_DATE]), 7), 'DD/MM/YYYY'), '', 'ALERT *Missing Fridays Data for jobs')))))))

this seems over complex but would work guess.
i just think there must be a simple way to compare a date to a date with time column and display a message in there.
=if (DATE(TODAY() - MOD(WEEKDAY(TODAY()) + 2, 7) - 1, 'DD/MM/YYYY') = DATE(FLOOR([JOB_DATE])), 'DD/MM/YYYY'), '', 'ALERT *Missing Fridays Data for jobs')

im guessing this isnt working because it is comparing todays date with a list of dates(array)?

I know that I can see 28/03/2025 11:02:22 in the JOB_DATE but if i use this it shows it as a string.
If( Len(Num(JOB_DATE))=5 , 'Date', 'String')
but it behaves as a date (and time)

hence im a little lost how to get it to behave 🙂

Re: If statement with date

Or — Thu, 03 Apr 2025 11:40:28 GMT

Your original approach should work (though the Date() wrappers for example are not necessary) if you just fix the Max(), I think?

Re: If statement with date

Jacktheripperz — Thu, 03 Apr 2025 12:08:01 GMT

To answer my own question in case anyone else wants to have a warning about missing data 🙂

this works nicely to warn me of missing data

IF(Index(Trim(Concat(DISTINCT Date(Floor([JOB_DATE]), 'DD/MM/YYYY'), ', ')), Date(Today() - Mod(WeekDay(Today()) + 2, 7) - 1, 'DD/MM/YYYY')) > 0, '', 'ALERT *Missing Fridays Data for jobs')