Networkdays function does not returning syntax error in load script

[ashis]

Hi,

In my data set i have only one date column . I am creating one composite key (concatenating  few columns ) as

distintct xx &'-'&  yy &'-'&  zz as key

in the fact table.

then creating one  table to calculate day difference 

temp:

load

key,

min(date),

max(date),

NetWorkDays(DATE(MAX(DATE),'DD/MM/YYYY'),DATE(MIN(DATE),'DD/MM/YYYY')

resident table1;

when loading this script , i am getting syntax error . I have tried this  NetWorkDays(max(date),min(date)) but same error. 

In the front end i have created a chart and used the above formula as expression , however it does not gives any error but is giving wrong number e.g max(date) =12-02-2019 and min (date) = 12-02-2019 , return same and networkdays return =1 .

Could you please help me where i am doing wrong.

[YoussefBelloum]

Hi,

you have a missing bracket on your script:

NetWorkDays( DATE(MAX(DATE),'DD/MM/YYYY') , DATE(MIN(DATE),'DD/MM/YYYY') '==> you forgot this one )'

[ashis]

Hi, I am sorry, I did not get you. I am using the following syntax NetWorkDays(DATE(MAX(DATE),'DD/MM/YYYY'),DATE(MIN(DATE),'DD/MM/YYYY')) as AGE syntax is fine , I am not getting any syntax error. Could you please tell me where you want me to put the ' '

[PrashantSangle]

If you are using aggregation function in script then you need group by clause. Try below

temp:
load key,
min(date),
max(date),
NetWorkDays(DATE(MAX(DATE),'DD/MM/YYYY'),DATE(MIN(DATE),'DD/MM/YYYY')) as Age
resident table1
Group by key
;

Regards,

[ashis]

Thank you Prashant, it worked.

I noticed the network days is not calculating the correct number of days, for example. it is calculating 1 day between

01/01/2019 and 01/01/2019 …. (dd/mm/yyyy)

[jonathandienst · Accepted solution]

You are mixing case (DATE is not the same field as date), and missing a group by on the load. I would do it like this:

temp:
load *,
	NetWorkDays(minDate, maxDate) as nwdays;
load
	key,
	min(date) as minDate,
	max(date) as maxDate,
resident table1
group by key;

[PrashantSangle]

Yes it is working as expected. see in help menu.
You can also try Interval() to calculate days difference between start date and end date

[Vegar]

---

@ashis wrote:

[…]I noticed the network days is not calculating the correct number of days, for example. it is calculating 1 day between

01/01/2019 and 01/01/2019 …. (dd/mm/yyyy)

---

This is the correct calculation as you have not listed January 1 as a holiday.

The networkdays function returns the number of working days (Monday-Friday) between and including start_date and end_date taking into account any optionally listed holiday.

https://help.qlik.com/en-US/sense/February2019/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTim...

[ashis]

Hi,

Thank you for your reply.

as suggested , I am using the following syntax;

temp:
LOAD*,
NetWorkDays(STARTDATE,LASTDATE) AS AGE;
load
AdjustmentKey,
min(DATE) AS STARTDATE,
max(DATE) AS LASTDATE
Resident table
Group by Key;

However the AGE field that is getting calculated in the temp table is seems incorrect. Same day difference is counting 1 , where as if I calculate the this in front end

Interval(DATE(LASTDATE,'DD/MM/YYYY') - DATE(STARTDATE,'DD/MM/YYYY'),'D')

it shows 0 for same day.

Is something I am doing wrong .

[ashis]

Hi Vegar,

Thank you for your input. It makes sense.