topic Changing Duration From Calendar Days to Working Days in QlikView

Changing Duration From Calendar Days to Working Days

lee_chandler — Wed, 25 Nov 2020 16:16:04 GMT

Hi I need to change the duration in my dashboard from calendar days into working days.

I know I need to use the Network days function however I've never used this before being rather new to the qlikview world.

this is my current load for the Duration:

IF(WI_AED>0,(Timestamp(TIMESTAMP#(WI_AED&' '&WI_AET,'YYYY-MM-DD hh:mm:ss'))-Timestamp(TIMESTAMP#(WI_CD&' '&WI_CT,'YYYY-MM-DD hh:mm:ss'))),(utc()-Timestamp(TIMESTAMP#(WI_CD&' '&WI_CT,'YYYY-MM-DD hh:mm:ss')))) AS WI_DURATION,

Re: Changing Duration From Calendar Days to Working Days

sunny_talwar — Wed, 06 Apr 2016 15:30:59 GMT

Do you need your duration in Days and Hours or just days?

Re: Changing Duration From Calendar Days to Working Days

lee_chandler — Wed, 06 Apr 2016 15:40:43 GMT

Hi Sunny, days and hours.

Re: Changing Duration From Calendar Days to Working Days

swuehl — Wed, 06 Apr 2016 15:48:40 GMT

Try

IF(WI_AED>0,

Networkdays(TIMESTAMP#(WI_CD&' '&WI_CT,'YYYY-MM-DD hh:mm:ss'), TIMESTAMP#(WI_AED&' '&WI_AET,'YYYY-MM-DD hh:mm:ss')),

NetWorkdays(TIMESTAMP#(WI_CD&' '&WI_CT,'YYYY-MM-DD hh:mm:ss'), utc() )

) AS WI_DURATION,

Re: Changing Duration From Calendar Days to Working Days

swuehl — Wed, 06 Apr 2016 15:50:11 GMT

If you need the working time, given a begin and end time on each working day, you can look into

Re: Calculate hours between two Date/Time strings

Re: Changing Duration From Calendar Days to Working Days

sunny_talwar — Wed, 06 Apr 2016 15:51:50 GMT

Stefan, if we need just days, why not just use WI_CD?

IF(WI_AED>0,

Networkdays(Date#(WI_CD, 'YYYY-MM-DD'), Date(WI_AED, 'YYYY-MM-DD')),

NetWorkdays(Date#(WI_CD, 'YYYY-MM-DD'), UTC())) AS WI_DURATION,

Re: Changing Duration From Calendar Days to Working Days

swuehl — Wed, 06 Apr 2016 16:02:03 GMT

That's right,

you only need the date part, and if that's what WI_CD is describing (and it looks like this), this will be enough.