Last Friday of the Month Variable

[alex-wb]

Hi All,

I am trying to create  4 Let variables in Qlik Sense that highlight the start and end of my financial month, and also the start and end of my financial year. 

The end of my financial month is the last Friday of the month we are in Eg. If we are in April, then the end of the month is 23rd of April since it falls on the last Friday.

The start of my new financial month would essentially be end of month +1 so using the April example it would be the 24th of April. 

The start of year is a bit more difficult. The financial year runs from March to April, but it end on the last Friday of the year. So for this financial year it will end on the 25th of March and the next year will start of the 26th. 

I am only trying to identify the date, and am not wanting to create tables etc, just let variables.

Would really appreciate the help with this.

Thanks,

Alex

[jwjackso · Accepted solution]

The last Friday of April 2021 is April 30, 2021.  Using the formula below, you can get the last day of the month by varying the order of days in bold.  If you want to get the previous Friday, just subtract 7 from the MonthEnd value.

=Date(MonthEnd(Today(1)) - Match(WeekDay(MonthEnd(Today(1))),'Sat','Sun','Mon','Tue','Wed','Thu'),'M/D/YYYY')

[alex-wb]

Hi JW,

Thanks for the code. I managed to work out what I needed to do to identify the month end and start and wrote the below code, but it's great to see another way to do it.

vFY_Month_Start = WEEKSTART(Monthstart(today()), 0, 5);
let vFY_Month_End = WEEKEND(Monthend(today()), 0, 5);

Thanks,

Alex