topic Re: Networkdays wrong from script, correct in UI in QlikView

Networkdays wrong from script, correct in UI

Michiel_QV_Fan — Wed, 02 May 2018 13:56:15 GMT

I calculate networkdays in the script but the results are wrong while using the same dates in a table in the UI are calculated correct.

These 2 dates: 20/3/2018 to 11/4/2018

calculated in the script as 22 days

and in the UI as 17 days

17 Days is correct.

Dates are loaded in this format 20.03.2018 but changed with this statement: date(date#(<datefields>,'DD.MM.YYYY')'DD/MM/YYYY')

I need the calculation in the script.

QlikView 12 SR3 versio 12.20.20400.0

Re: Networkdays wrong from script, correct in UI

rubenmarin — Wed, 02 May 2018 13:56:15 GMT

Hi Michiel, I make a document with this script:

LOAD NetWorkDays('20/03/2018','11/04/2018') as value

AutoGenerate 1;

value loads '17'. Can you post a sample that shows the problem, I can't reproduce it (I'm in different version)

Re: Networkdays wrong from script, correct in UI

Michiel_QV_Fan — Wed, 02 May 2018 14:09:15 GMT

See original post for the example.

I tried this also and hardcoded dates in the correct format does calculate correct.

But I expect qlikview to do the same with the date fields that are edited with date and date# functions.

Re: Networkdays wrong from script, correct in UI

sasiparupudi1 — Wed, 02 May 2018 14:16:37 GMT

NetWorkDays(Date#('20.03.2018','DD.MM.YYYY'),Date#('11.04.2018','DD.MM.YYYY')) gives me 17

Re: Networkdays wrong from script, correct in UI

rubenmarin — Wed, 02 May 2018 14:18:30 GMT

It should, and this works too:

LOAD NetWorkDays(Date(date#('20.03.2018', 'DD.MM.YYYY'),'DD/MM/YYYY'),Date(date#('11.04.2018', 'DD.MM.YYYY'),'DD/MM/YYYY')) as value

AutoGenerate 1;

That's why I'm askjing for a sample, there should be something else, I'm not sure if the different version is the only reason.

Re: Networkdays wrong from script, correct in UI

Michiel_QV_Fan — Wed, 02 May 2018 14:34:02 GMT

I do not have just 2 dates, the 2 dates mentioned are just examples.

I have 2 fields with all the dates.

Re: Networkdays wrong from script, correct in UI

Michiel_QV_Fan — Wed, 02 May 2018 14:35:52 GMT

Strange why setting the date formats inside the networkdays function does the trick.

I did a preceding load thus calling the edited date formats and that does not work.

Do you know why?

Re: Networkdays wrong from script, correct in UI

rubenmarin — Wed, 02 May 2018 14:35:52 GMT

Preceding load also returns me '17'. I tried with:

LOAD NetWorkDays(Date1,Date2) as value;

LOAD

Date(date#('20.03.2018', 'DD.MM.YYYY'),'DD/MM/YYYY') as Date1,

Date(date#('11.04.2018', 'DD.MM.YYYY'),'DD/MM/YYYY') as Date2

AutoGenerate 1;

I tried a few things the retrive that '22' but I didn't accomplish, the only way is doing Date2-Date1, so that 22 it's counting all dates, not removing weekends.