Count Days If Working Day

[rohi__]

Hello everyone,

I couldn't find a solution for my problem so I created my own Question.

I'd like to get the amount of days which expired during the period "StartDate" to "EndDate" if it was a working day.

So I got my Calendar with:

Date | Workday

1/1/2019 | 0

1/2/2019 | 0

1/3/2019 | 1

1/4/2019 | 1

1/5/2019 | 1

1/6/2019 | 0

And the Orders Table:

Order | StartDate | EndDate

A | 1/1/2019 | 1/6/2019

B | 1/3/2019 | 1/4/2019 

I'd like to create a measure or a column in the orders table.

Is there something like "CountDays(From = StartDate, To = EndDate, IF ( Workday = 1))"?

Thanks in Advance!

Regards,

rohi__

[JordyWegman]

Hi Rohi,

Qlik has a function for this called Networkday() see the help.

You enter the start date and end date and it will calculate this for you.

Jordy

Climber

[rohi__]

Hi Climber,

thanks for your fast Reply!

I've tried it with the NetWorkDays() function, but the problem is that i've got specific workdays.

The function just removes the weekends in my understandings. 

Do you got another idea?

Thanks again!

[JordyWegman]

Yes, but you can still use this.

Expand your calendar with a concat function and use this as the 'holiday' part. Here you exclude all the days you don't want to have (all the 0's).

Data:
Load
	Concat(Date,',') as DateWorkday
Where Workday = 0
;
load* Inline [
Date , Workday
1/1/2019 , 0
1/2/2019 , 0
1/3/2019 , 1
1/4/2019 , 1
1/5/2019 , 1
1/6/2019 , 0
];

Jordy

Climber

[rohi__]

Allright.

In the DateWorkday i have to include all days with "workday = 0"?

But the holiday expression is working with holidaystart, holidayend, right?

How do I know from when until when the holidays are when I only have a list with zeros?

I actually need the first and last zero before another one comes.

Do you got an Idea for this?

I'm sorry for all the questions, I really appreciate it!

Regards,

rohi__

[martinpohl]

Two options:

As mentioned before, networkdays(Start,End,List of holidays) is an option if you want to count only days from monday - friday because networkdays don't count saturdays and sundays in general.

With your datas you can use an expression like

sum(if(Date>=StartDate and Date <=EndDate and Workday=1,1,0))

Regards

[JordyWegman]

Nope, the holiday works with a concatenated string of dates. See the Qlik help for this.

What I do with the 0, is make a concatenated list of all your dates that you don't want to include. With this, we think the otherway around. See it as this, you have you start date and end date. But you have specific days that you want to count the 1's, but then also specific days you don't want, the 0's.

With the holiday function we can only exclude and not include. So instead of using the 1's we need to include we have to use the 0's for this to exclude.

Do you get this idea?

This is how the function will look (and the long sentence is DateWorkday) NetworkDay(StartDate, EndDate, AllTheExtraDaysYouWantToExclude).

Jordy
Climber

[Vegar]

Hi @rohi__
I solved exactly the same issue for @Den Tuesday. Maybe he/she have more input for you in this matter.

My solution is found here:
https://community.qlik.com/t5/Qlik-Sense-App-Development/Calculated-column-count-days-between-two-da...

[rohi__]

Thanks for your answer Jordy!

got the idea but the problem is that i got workdays at Weekends aswell, and networkdays is exluding them anyhow.

[rohi__]

Thanks for your answer Martinpohl!

NetWorkDays() is not working because i got workdays on weekends...

With your second option i got an invalid visualization.

Do you got another idea?

Thanks alot!

Regards