Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
JonesBeach
Contributor III
Contributor III

Unable to calculate business days

Hi Everyone,

I am trying to calculate business days between a column from a table and current date(from the date in the field to whatever is the current date). This is the formula i am using:

NetworkDays:

load

ID,

NetWorkDays(Date_start, Now()) as Age_of_ID

Resident Info;

 

My date column is in this format -> Dec 05 2020 5:00PM.

As for now I am not sure where exactly am I making the mistake but I am unable to count the business days.

Also for the holidays to be accounted for the calc of the business days, we have a table importing from the SQL database. 

I ultimately want a column with business days until the current day. thanks!

 

Labels (2)
11 Replies
anat
Specialist
Specialist

can u try like :NetWorkDays(date(Date_start'DD/MM/YYYY'), date(today(),'DD/MM/YYYY')

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

JonesBeach
Contributor III
Contributor III
Author

Hi Anat, No luck. 

anat
Specialist
Specialist

try to make Date_start and today() in same format the use networkdays

anat
Specialist
Specialist

load *,networkdays(dt1,today()) as diff;
load *,(Date(Date#(dt,'MMM DD YYYY hh:mmtt'),'MM/DD/YYYY')) as dt1,today();
load * inline [
dt
Dec 05 2020 5:00PM
];

JonesBeach
Contributor III
Contributor III
Author

Hi Anat, thanks for that response but when I apply that code it returns an empty column for some reason. I am not sure whats happening. I am very new to QlikSense and have a background in R and Power bi. Qlik has a bit different syntax than what I know from before.

anat
Specialist
Specialist

can you post some sample data,so that i will apply logic and will share the code with you....

JonesBeach
Contributor III
Contributor III
Author

Hi I have taken the field values straight from the QlikSense so you can see what exactly is in thr. All what I need to do is to calculate the age of the SKU until the current date.  Timestamp is not important. Thanks again for taking the time out to help me with this!

 

anat
Specialist
Specialist

Directory;
load *,networkdays(dt1,today()) as diff;
load *,(If((substringcount(left(Start_Date,12), ' '))=4,

date(Date#(left(Start_Date,12),'MMM D YYYY'),'MM/DD/YYYY'),

date(date#(left(Start_Date,12),'MMM D YYYY'),'MM/D/YYYY')) ) as dt1,today()
;
LOAD ProdID,
Start_Date
FROM
[..\Downloads\Data For NetWorkDays.xlsx]
(ooxml, embedded labels, table is Data);

JonesBeach
Contributor III
Contributor III
Author

Hi Thanks for the solution however, it is still showing null for some values could you pls elaborate on that a bit and also what if i want to incorporate a list of holidays in the NetWorkDays formula?  Thanks!