Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rohi__
Contributor III
Contributor III

Count Days If Working Day

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__

Labels (4)
16 Replies
rohi__
Contributor III
Contributor III
Author

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!

Channa
Specialist III
Specialist III

try to crete variable and pass working days into it

networkingdays(start,end,variable)

Channa
JordyWegman
Partner - Master
Partner - Master

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

Work smarter, not harder
Vegar
MVP
MVP

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

 

Script using intervalMatchScript using intervalMatchResult in table using different total methodsResult in table using different total methods

 Ping: @Den

Vegar
MVP
MVP

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. )
JordyWegman
Partner - Master
Partner - Master

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

Jordy

Climber

Work smarter, not harder
rohi__
Contributor III
Contributor III
Author

Thank you guys @JordyWegman  @Vegar  for your help!

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

Thanks again!

Regards