topic Re: Count Days If Working Day in App Development

Count Days If Working Day

rohi__ — Sat, 16 Nov 2024 20:43:02 GMT

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__

Re: Count Days If Working Day

JordyWegman — Thu, 06 Jun 2019 08:55:43 GMT

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

Re: Count Days If Working Day

rohi__ — Thu, 06 Jun 2019 08:58:36 GMT

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!

Re: Count Days If Working Day

JordyWegman — Thu, 06 Jun 2019 09:13:12 GMT

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

Re: Count Days If Working Day

rohi__ — Thu, 06 Jun 2019 09:25:41 GMT

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__

Re: Count Days If Working Day

martinpohl — Thu, 06 Jun 2019 09:29:14 GMT

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

Re: Count Days If Working Day

JordyWegman — Thu, 06 Jun 2019 09:37:36 GMT

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

Re: Count Days If Working Day

Vegar — Thu, 06 Jun 2019 09:53:10 GMT

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-dates-on-calendar-table/m-p/1588338/highlight/true#M43073

Re: Count Days If Working Day

rohi__ — Fri, 07 Jun 2019 06:49:00 GMT

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.

Re: Count Days If Working Day

rohi__ — Fri, 07 Jun 2019 06:51:10 GMT

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

Re: Count Days If Working Day

rohi__ — Fri, 07 Jun 2019 07:20:48 GMT

Thanks for your answer Vegar!

I tried the logic behind your Code, it's working. But the performance is really bad... The code ran the whole night..

Is there another way?

Thanks for your reply!

Re: Count Days If Working Day

Channa — Fri, 07 Jun 2019 07:47:36 GMT

try to crete variable and pass working days into it

networkingdays(start,end,variable)

Re: Count Days If Working Day

JordyWegman — Fri, 07 Jun 2019 08:12:07 GMT

Hi,

This is your solution. You can use IntervalMatch for this. Try the following script:

[YourData]: 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 ]; [Data]: Load * Inline [ Order , StartDate , EndDate A , 1/1/2019 , 1/6/2019 B , 1/2/2019 , 1/4/2019 ]; Inner Join IntervalMatch (Date) Load StartDate, EndDate Resident Data;

Then add the following formula:

Count({$< Workday = {1}>}Workday)

Results in the following:

Jordy

Climber

Re: Count Days If Working Day

Vegar — Fri, 07 Jun 2019 08:19:59 GMT

Sure there is another way to solve this. Lets try using IntervalMatch(). See attached qvf

Script using intervalMatchResult in table using different total methods

Ping: @Den

Re: Count Days If Working Day

Vegar — Fri, 07 Jun 2019 08:45:05 GMT

It looks like me and @JordyWegman thought of a similar approach. The main difference between our two solutions is how many days that should be included in the interval. In Jordy's all days including start and end date are included, in my the first day is excluded (requirement in the similar post by @ Den. )

Re: Count Days If Working Day

JordyWegman — Fri, 07 Jun 2019 09:05:35 GMT

Yes, good solution @Vegar, that depends on the whishes of the rohi.

Jordy

Climber

Re: Count Days If Working Day

rohi__ — Fri, 07 Jun 2019 11:04:05 GMT

Thank you guys @JordyWegman @Vegar for your help!

I will implement the codes soon and will mark your answers as solution!

Thanks again!

Regards