Problem with dates - philosophical use of minus

[josephinetedesc]

Hi all

so should I put in a negative to get this right?

For example (ignore weekends):

22 Jan MINUS 8 Jan  positive 12 days.  Why does Excel show negative 11(ok showing networking days), and QlikView show 0???

date format is (SET DateFormat='D/MM/YYYY')

Thank you.

Jo

Patient	count(CTAct_DtTm)	count(QAAct_DtTm)	min(CTAct_DtTm)	max(CTAct_DtTm)	min(QAAct_DtTm)	max(QAAct_DtTm)	NetWorkDays(max(QAAct_DtTm), max(CTAct_DtTm))	JO SUMS	EXCEL
P	1	2	8/01/2016 13:32	8/01/2016 13:32	19/01/2016 12:00	22/01/2016 11:45	0	???	-11

[swuehl]

In QV, the arguments are expected first argument = start date, second argument = end date:

networkdays (start:date, end_date {, holiday})

Returns the number of working days (Monday-Friday) between and including start_date and end_date taking into account any optionally listed holidays. All parameters should be valid dates or timestamps.

Examples:

networkdays ('2007-02-19', '2007-03-01') returns 9

networkdays ('2006-12-18', '2006-12-31', '2006-12-25', '2006-12-26') returns 8

Hence in your case, it should be

NetWorkDays(max(CTAct_DtTm),max(QAAct_DtTm) )

Not sure about your 12 days, how have you calculated this?

[josephinetedesc]

Thank youSwuehl

my calcs look much better now, however why does this happen?

ie why do I not get a value in this situation when I subtract 18/1/2016 from 1/02/16?  the networkDays are correct but the subtraction does not work?  What am I missing?  Is Qlikview seeig text perhaps???

Jo

Jo

Patient	MaxCT	maxQA	NetWorkDays(max(CTAct_DtTm),max(QAAct_DtTm))	QAAct_DtTm -   CTAct_DtTm
x	18/01/2016   16:26	1/02/2016   :	11	-

[swuehl · Accepted solution]

If there are more than one distinct value for the field(s) per dimension line, QV will return NULL.

Use aggregation functions in expressions, maybe Max() function?

[josephinetedesc]

Thank you it worked.  So

1.     my networkding days formula was wrong

2.     I had to use Max(date1) - max(date2)

Thank you

Jo