How to include only working days / weekdays in a Qlikview report?

[Lilla_Szabo]

Hi Everyone?

Is it possible in Qlikview to limit my data to working days / weekdays (weekends not needed)?

This report is about approval time and creation time of POs and PRs from 2012 until current day.

So it would be great to avoid manual work. 🙂

I would like to limit all the objects on 3 sheets of the whole report, so it would be great to do it on a script level somehow.

Is it possible?

If not, how can I do it on an object level? 

Thank you in advance for the help! 🙂

[Or · Accepted solution]

Have a look at using the built-in workdate functions. These may work for you depending on what exactly you're trying to achieve - it isn't quite clear from your post.

https://help.qlik.com/en-US/qlikview/May2023/Subsystems/Client/Content/QV_QlikView/Scripting/DateAnd...

https://help.qlik.com/en-US/qlikview/May2023/Subsystems/Client/Content/QV_QlikView/Scripting/DateAnd...

https://help.qlik.com/en-US/qlikview/May2023/Subsystems/Client/Content/QV_QlikView/Scripting/DateAnd...

[Lilla_Szabo]

Thank you for the help, NetWorkdays looks something I need! 🙂

However I would like to include not the dates, but used for a formula. Is it also possible?

So for example I calculate the Total Purchase Requests Approval TAT Days like: 

=num(avg([Change Date]-[Requisition Date]),'#,##.0')

Is it possible to include NetWorkdays in this?

In this case the Change Date and the Requisition Date is always different per PR, so I can not add an exact date.

Thank you in advance for the answer! 🙂

[Or]

You should be able to use NetWorkDays([Requisition Date],[Change Date]) , I believe?

[Aasir · Accepted solution]

In your Calendar table add a field like below
DayName(Date) as DayOfWeek,

and use below to load only the data for working days by excluding weekends

WorkingDaysData:
LOAD
*
RESIDENT YourOriginalTable
WHERE DayOfWeek <> 'Sat' AND DayOfWeek <> 'Sun';

use the WorkingDaysData table in your charts and objects for the report.

[Lilla_Szabo]

Thank you for the help! 🙂

[Ahidhar]

try this for your Date field

load
Date,
WeekDay(Date) as weekday
resident Date where not Match(weekday(Date),'Sat','Sun');drop table Date;