Unable to calculate business days

[JonesBeach]

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!

[anat]

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]

Hi Anat, No luck. 

[anat]

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

[anat]

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]

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]

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

[JonesBeach]

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]

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]

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!