topic Re: Finding MonthstartDate excluding Saturday and Sunday in Qliksense in App Development

Finding MonthstartDate excluding Saturday and Sunday in Qliksense

SunainaUmberkar — Mon, 08 Jan 2024 09:52:24 GMT

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.

Re: Finding MonthstartDate excluding Saturday and Sunday in Qliksense

marcus_sommer — Mon, 08 Jan 2024 13:51:37 GMT

Try it with:

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

Re: Finding MonthstartDate excluding Saturday and Sunday in Qliksense

SunainaUmberkar — Fri, 12 Jan 2024 01:17:59 GMT

Hello Marcus,

Thank you. It's working fine.

Re: Finding MonthstartDate excluding Saturday and Sunday in Qliksense

SunainaUmberkar — Mon, 29 Jan 2024 07:29:22 GMT

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.

Re: Finding MonthstartDate excluding Saturday and Sunday in Qliksense

marcus_sommer — Mon, 29 Jan 2024 08:14:43 GMT

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.