Counting number of workdays from dates selected on date picker

[Ciara]

How can I ascertain the number of workdays based off the dates picked from a date picker?

[Taoufiq_Zarra]

Maye be :

=networkdays(GetFieldSelections(Date),today())

[Ciara]

Hi Taoufiq

Thanks for the response. 

The code below works great if you just want the week days between a date picked and today.

My date picker is set to range.  Is there a way of getting the 'start date' picked and the 'end date' picked.

Thanks again

Ciara

[mdmukramali]

Hi,

can you try like this

=networkdays([start date], [finish date])

if you are using the variable to store start date and end date from date range picker then try 

=networkdays($(vStartDate) ,$(vFinishDate))

Thanks,

Mohammed Mukram

[Ciara]

Hi Mohammed 

Thanks for the response. How would I store the dates picked from the date picker in a variable?

Thanks

Ciara

[tresesco]

If you are selecting a range of dates using picker, you should be able to get the min and max dates out of it and use them as start and end date respectively. Try something like:

Networkdays( Min(DateField), Max(DateField) )

[Ciara]

Thanks Tresesco.  This works if there is data for my date field.

It is a ScheduledOperationDate.  How do I count each day even if there is no data for that date? (ie. no operations were scheduled for that date)

I thought if I used the floor function this would work but unfortunately it doesn't.

[mdmukramali]

Hi,

In that case you have to generate missing dates by MasterCalender, which contains all the dates from Min(ScheduledOperationDate) to Max(ScheduledOperationDate) 

[tresesco]

Isn't your ScheduledOperationDate a field in data model? There are two aspects of counting no. of days between two dates:

• you can just count the dates in between from the same date field using count(), or
• let qlik do it using simple Date1-Date2 logic that doesn't require date field to have all dates in between

Networkdays() is such a function that looks for only two dates and does it's own calculation without having to depend on the field date values. And using count()(the first option) is hardly used in real scenario, I believe.

[Ciara]

Yes ScheduledOperationDate is a field in my data model.

I was thinking about this.  I'm trying to ascertain the number of workdays for each operating theatre.  I think this is why I'm getting wrong results because the dates are only based on if operations took place in an OR.

I guess I need an independent date field not associated with my data model and then just do my calculation on that....

The thing is.... 😄 I want the date picker to give me all the results for my theatre sessions but separately count the number of work days between those dates regardless of whether an operation took place or not.  Can you have 2 master calendars in an app? ?? and then link those to 1 date picker????  (Lordy lord.... I'm getting more confused 😂)

My results looks like this currently because my dimension is set to OperatingRoom.  My filter is set to 5 days (15th to 19th June) and my calculation for available time is based on a duration of 9hrs for each date.

But because there were days in this week that no operations happened in OR02, OR05 and OR06 it is only calculating the available time on the days there is data for the OR.

What I'd like to see below in the Available Time is 45:00 for each OR regardless of whether theres any data for that OR for a date.