topic Negative values in networkdays in QlikView

Negative values in networkdays

slsaagar — Mon, 06 Apr 2015 14:16:09 GMT

Hi,

I am using the networkdays formula to calculate the number of networkdays between two dates as 'difference'. i want difference-1, so am using formula like

networkDays(billed_date,shipping_date)-1,

The numbers are showing correctly as long as the difference is a positive number, but when it is a negative number all I get is -1, irrespective of the actual difference.

please.... help to get the correct negative numbers

thanks..

Re: Negative values in networkdays

avinashelite — Mon, 06 Apr 2015 14:54:27 GMT

As per my knowledge , Networking days function always give the positive result and if it has any negative values then it will show as 0 and since your using -1 it will show as -1, try like this

if(billed_date>shipping_date,networkingdays(shipping_date,billed_date),'-&(networkingdays(billed_date,shipping_date)-1))

Re: Negative values in networkdays

giakoum — Mon, 06 Apr 2015 14:56:00 GMT

this is how the function works.

For example the following returns 0 :

networkdays ('2007-02-19', '2007-01-01')

Try an If statement : if function returns zero then calculate the same but with the dates in different order :

if(networkdays ('2007-02-19', '2007-01-01') = 0, networkdays ('2007-01-01', '2007-02-19') * -1, networkdays ('2007-02-19', '2007-01-01'))

Re: Negative values in networkdays

Anonymous — Mon, 06 Apr 2015 15:05:34 GMT

The first parameter to NetworkDays() is the start date and the second parameter is the end date.

If the the end date supplied is before the start date, then this is by definition invalid as something cannot end before it starts and a value of zero is returned.

So try using something like this :

= if ( billed_date> shipping_date,

( NetWorkDays ( shipping_date, billed_date) * -1 ) + 1 ,

NetWorkDays ( billed_date, shipping_date) -1 )

Re: Negative values in networkdays

slsaagar — Mon, 11 May 2015 08:05:01 GMT

thank u all for ur replies this formula worked for my creteria...

if(billeddate<=shippingdate,

NetWorkDays(billeddate,shippingdate)-1,(NetWorkDays(shippingdate,billeddate)*-1)-1)

Re: Negative values in networkdays

Anonymous — Wed, 30 Mar 2016 10:34:23 GMT

sagar sl wrote:

thank u all for ur replies this formula worked for my creteria...

if(billeddate<=shippingdate,

NetWorkDays(billeddate,shippingdate)-1,(NetWorkDays(shippingdate,billeddate)*-1)-1)

Well, this is still wrong.
Let's say billeddate = 3/4/2016

shippingdate = 4/4/2016

So, expression would be

=if(date(3/4/2016)<=date(4/4/2016),

NetWorkDays(date(3/4/2016),date(4/4/2016))-1,(NetWorkDays(date(4/4/2016),date(3/4/2016))*-1)-1)

And the result would still be -1

It can be simply done like this

If(NetWorkDays(billeddate ,shippingdate )=0,NetWorkDays(billeddate ,shippingdate ),NetWorkDays(billeddate ,shippingdate )-1)

Assumption : shippingdate >= billeddate

If that's not the case,
It should be

If(NetWorkDays(billeddate ,shippingdate )=0,If(NetWorkDays(shippingdate ,billeddate )=0,0,NetWorkDays(shippingdate ,billeddate )-1),NetWorkDays(billeddate ,shippingdate )-1)