topic Re: Checking Max Date with Latest Friday's Date in QlikView

Checking Max Date with Latest Friday's Date

Anonymous — Fri, 12 Aug 2016 23:32:44 GMT

I need some help in comparing the dates with Max date in the data,

Every Friday the data refreshes ,

For example taking today’s date(8/12/2016)

Max Date in Data is 8/12/2016,

Max Date if data is NOT refreshed is 8/5/2016 ( last Friday) , whenever the data is NOT refreshed I need to display an alert saying that daily did not refresh

Conditions I need to check ,

If its Monday (8/15) : check is max date is last Friday (8/12/2016)

If its Tuesday (8/16) : check is max date is last Friday (8/12/2016)

If its Wednesday (8/15) : check is max date is last Friday (8/12/2016)

If its Thursday (8/15) : check is max date is last Friday (8/12/2016)

If its Friday (8/16) : check is max date is last Friday (8/16/2016) /* If it’s NOT refreshed the date would be 8/12/2016 , this is when the alert appears until the data is refreshed which might take more than 1 day to fix the root cause of the failure so this alert has to stay next day too*/

If its Saturday (8/17) : check is max date is last Friday (8/16/2016)

If its Sunday (8/18) : check is max date is last Friday (8/16/2016)

If its Monday (8/19) : check is max date is last Friday (8/16/2016)

I was trying to write the expression as below to put it in the layout of the text box with alert message but this seems not work as expected ,

IF(((date(floor(weekend(today())-9)) <> max({1} Date)) OR (date(floor(weekend(today())-2)) <> max({1} Date))),
0,
1)

Re: Checking Max Date with Latest Friday's Date

marcus_sommer — Sat, 13 Aug 2016 07:00:50 GMT

Try this:

if(today() - max({< Weekday = {'Fr'}>} Date) > 6, 1, 0)

which meant that you need Weekday as field within your datamodel but it is a very common field included in the most master-calendars.

- Marcus

Re: Checking Max Date with Latest Friday's Date

swuehl — Sat, 13 Aug 2016 08:38:51 GMT

I think the condition in

IF(((date(floor(weekend(today())-9)) <> max({1} Date)) OR (date(floor(weekend(today())-2)) <> max({1} Date))),0,1)

is always TRUE.

I would also just check for the difference between Today() and the last date in your records, and you probably don't even need to check for the weekday:

=If( ( Today(2) - Max({1} Date) ) >6 ,1,0)

I am not sure what happens with your data if a reload does not happen, then you do a reload manually on saturday and then the following week the reload does not work again. Could be that the alert is popping up 1 day late.

Maybe you can also use:

=If( Weekstart(Today(),0, 4) > Max({1} Date), 1,0)