topic Re: Problem With NetWorkDays Function in QlikView

Problem With NetWorkDays Function

Wed, 25 Nov 2020 16:16:04 GMT

Hello Guys,

I have this table:

On Qlikview to create the field number D to get the number of working days that have elapsed, I have used this script:

NetWorkDays(BSTORE4A, TIMESTAMP4A) - 2 + (1 - Frac(BSTORE4A)) + Frac(TIMESTAMP4A) AS GIORNI_PASSATI_4B

The script work correctly, but I have 5 values that are negative, without any reason, and I don't understand why. Someone can I help me?

Thank You

Re: Problem With NetWorkDays Function

m_woolf — Thu, 25 May 2017 15:31:17 GMT

In you formula, you refer to a field named TIMESTAMP4A, but in your attached pic you show a field named TIMESTAMP20

Re: Problem With NetWorkDays Function

Thu, 25 May 2017 15:39:42 GMT

Sorry, I did copy-paste from another script to test. The correct script is:

NetWorkDays(BSTORE20, TIMESTAMP20) - 2 + (1 - Frac(BSTORE20)) + Frac(TIMESTAMP20) AS GIORNI_PASSATI_4B

The problem remain. The script work fine but I have that strange 5 values

Re: Problem With NetWorkDays Function

antoniotiman — Thu, 25 May 2017 15:52:07 GMT

Hi Lorenzo,

NetWorkDays(BSTORE20, TIMESTAMP20) = 0

4/12/2016 is Sunday

Regards,

Antonio

Re: Problem With NetWorkDays Function

Thu, 25 May 2017 16:13:44 GMT

Ohhh man you're Amazing!!! I didn't think that the First date was on a festive day. Thanks you 😄

Re: Problem With NetWorkDays Function

m_woolf — Thu, 25 May 2017 16:37:27 GMT

For the first row:

NetWorkDays(BSTORE20, TIMESTAMP20) = 22, but you are getting the answer 0.

Look at your data in QlikView. I suspect that the values for BSTORE20 and/or TIMESTAMP20 are not valid timestamps in Qlikview (they are probably strings that look like timestamps). When you send them to Excel, Excel interprets them as timestamps.

Re: Problem With NetWorkDays Function

antoniotiman — Thu, 25 May 2017 17:21:06 GMT

Date/Timestamp Format is DD/MM/YYYY (Italy standard)

and 03/12-04/12/2016 are festive days.

Regards,

Antonio

Re: Problem With NetWorkDays Function

m_woolf — Thu, 25 May 2017 17:24:53 GMT

Re: Problem With NetWorkDays Function

antoniotiman — Thu, 25 May 2017 17:31:38 GMT

Are You setting date/Timestamp format to DD/MM/YYYY ?

3 dec 2016 and 4 dec 2016

Re: Problem With NetWorkDays Function

m_woolf — Thu, 25 May 2017 17:56:25 GMT

Antonio, That would have never occurred to me. I think you are correct.