Finding MonthstartDate excluding Saturday and Sunday in Qliksense

[SunainaUmberkar]

Hello,

I have a requirement where user refreshes data in Access Database on Weekday1 of the month. Now this excludes Sat or Sun. I have to implement same in Qliksense.

I have created a Monthstart Variable as 

Date(AddMonths(MonthStart(Today()),-1),'MM/DD/YYYY')

In the main table, i am applying this in the where condition as 

LOAD
ALCUTOFF,
ALUNIT,
ALPOOLID,
ALTRUIND
FROM Qvd 
where ALCUTOFF='$(vMonthStartDate1)';

This is working fine for Dec 2023,Jan 2024 till May 2024 month as monthstart date is on weekday. But in the future month like June 2024, Monthstart date is on Sat(01/06/2024). I need to exclude this Saturday,Sunday and show Monthstart date as '03/06/2024'. How can i do this?

Any help is appreciated. Thanks in advance.

[marcus_sommer · Accepted solution]

Try it with:

=lastworkdate(monthstart(today(),5),1)

[SunainaUmberkar]

Hello Marcus,

Thank you. It's working fine. 

[SunainaUmberkar]

Hello Marcus,

Is there a way to exclude holidays using lastworkdate function. I know we can provide an optional value as holiday, but we need to specify the holiday date. As for every month, holiday date will be different , how can I specify that in the script?

Thanks in advance.

[marcus_sommer]

The simple answer is - just add the list of weekend-days to the list of holidays. Lastly I did something similar for a customer project to emulate an intervalmatch-resolving purely within the Excel UI (without Power Query or macro-loops) just by applying multiple long lists of to excluding days to networkdays and similar functions. The same method of alienated the holiday-parameter should be working in Qlik. And in general there mustn't be much hard-coded because most of such listings could be generated.

Personally I suggest to consider a move of the logic to a master-calendar because there are much more possibilities as simply deriving a month or year from a date else working-days could be flagged and afterwards accumulated + aggregated (sum/min/max/...) and the first/last workingdays be flagged and many more. You could use thes information everywhere and it will be easier to pick the relevant ones from there (as the single source of truth) as applying the underlying logic each time when you need such an information.