Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
josephinetedesc
Creator III
Creator III

Problem with dates - philosophical use of minus

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

      

Patientcount(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 SUMSEXCEL
P128/01/2016 13:328/01/2016 13:3219/01/2016 12:0022/01/2016 11:450???-11
1 Solution

Accepted Solutions
swuehl
MVP
MVP

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?

View solution in original post

4 Replies
swuehl
MVP
MVP

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
Creator III
Creator III
Author

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

PatientMaxCTmaxQANetWorkDays(max(CTAct_DtTm),max(QAAct_DtTm))QAAct_DtTm -
  CTAct_DtTm
x18/01/2016
  16:26
1/02/2016
  :
11-
swuehl
MVP
MVP

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?

View solution in original post

josephinetedesc
Creator III
Creator III
Author

Thank you it worked.  So

1.     my networkding days formula was wrong

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

Thank you

Jo