Number consecutive rows based on conditions

[AnneEb]

Hello everyone!

I have a calendar table that looks something like this

%Date	Day	Month	Year	Weekday_NUM
01.01.2022	01	01	2022	5
02.01.2022	02	01	2022	6
...	...	...	...	...
09.02.2025	09	02	2025	6
10.02.2025	10	02	2025	0

I want to add a column to that that displays a single number for all working days of each month. So the 07.02.2025 should e.g. have the number '5' and today (10.02.2025) should have the number '6'. I want this count to start again with each month. 

Not much more else to say, I am completely clueless as to how I could attempt that. Any ideas are more then welcome! 

[Arek92 · Accepted solution]

Have you consider using the function NetWorkDays()? 

Assuming %Date is your date field the final expression could look like this:

=NetWorkDays(MonthStart(%Date), %Date))

https://help.qlik.com/en-US/sense/November2024/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTim...

[AnneEb]

Thank you so much, this did the trick. I tried this and while it gave me some multiple values (e.g. 07./08./09. January 2022 all having number 5), I could easily filter those out by filtering for Weekday_NUM < 5. 

[marcus_sommer]

Within the master-calendar create a workday-flag with something like:

if(match(weekday(Date), 5, 6), 0, 1) as Flag

and within a preceding to it might be applied:

if(YearMonth = previous(YearMonth), peek('X') + Flag, Flag) as X