YESTERDAY to exclude weekends

[Michael_Chappell]

Hi!

We have Yesterday set up as a Measure but it is currently looking at all days of the week. As a business we are closed on weekends so on a Monday we would like Yesterday to ignore Saturday and Sunday and look at Friday. Our Yesterday is currently built like this;

Script: IF( Day([Date]) = Day(Today()-1) AND
Month([Date]) = Month(Today()-1) AND
[Date] <= Today()-1, 'Y', Null() ) AS IsYESTERDAY,

Master Item: Sum( {<IsYESTERDAY = {'Y'}, InvoiceYear = {$(=Max(InvoiceYear))}>} GrossSales)

Any input would be much appreciated! 

[Or]

Try using a built-in function, e.g. FirstWorkDate(Today()-1,1)

[Michael_Chappell]

I wasn't aware of that function to be honest. Thank you!

The above expression works great however it shows a date rather than the GrossSales for Friday. Sorry, I should've been more specific in my original query. 

[Or]

That's the expression to get you the correct date for yesterday - you can use it in your script to create the Yesterday flags and then use the same expression you were already using.

[Michael_Chappell]

Hi Or,

Thank you for your response! It's probably me being dim, but I can't figure out how you'd change the script with this new function? Every time I try it gives me a YTD figure as opposed to a Yesterday figure. Do you know what the script should be?

[Or · Accepted solution]

Seems like you should be using:

IF(Date = FirstWorkDate(Today()-1,1) , 'Y') AS IsYESTERDAY

in your script, and then your existing set analysis should continue to use that in order to get yesterday's sales (or whatever else you're looking to do with this flag). Note that you may need to wrap both of those up in a Date() format and/or floor the existing Date if it contains a timestamp component in order to get those two sides to actually match.

[Michael_Chappell]

Wonderful, thank you for all your help!

I've inserted the tidbit of script you've sent over and it's worked a treat for today. The true test will be next Monday! 😉

Thanks again!

[Or]

You could test it before next Monday - just use Today()-2 rather than today()-1 (as of today, Tuesday) to simulate it being yesterday.

One quick note - your existing formula may give you trouble on January 1st, as your yesterday will be December 31st 2021 while your max(year(invoice)) could be 2022. If your organization doesn't issue invoices on January 1st (which is commonly a holiday) or data is always up to yesterday anyway, you're fine, though.

[Michael_Chappell]

Of course! 🤦‍♂️ My knowledge of Qlik is limited but I should've known that!

All tested and it works perfectly, you're the best!

Thank you for your note, although you're right in saying that we are always closed on January 1st as it is a holiday and therefore shouldn't be an issue.

Out of interest (don't worry, I'm not looking to do this as I imagine there would be a lot of work involved!) is it possible to predefine bank holidays and then have the Yesterday function skip passed these ones too?

[Or]

FirstWorkDate will accept a list of holidays. They can be manually typed in, or you can use a more robust approach such as the one detailed in e.g. this thread:

https://community.qlik.com/t5/QlikView-App-Dev/Exclude-holidays-using-Networkdays-in-script/td-p/566...

That one is for NetWorkDays() but it's the same thing insofar as holidays.