Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Count Num of days

Hi people

I have a situation and I could nog figure out how to solve this issue.

The dates RegisterdateTime en ClosedDateTime in the example below are Dates plus Time. In the example i let them away.

In my script i introduced a extra field named TurnAround. The code behind TurnAround is:

IF (CLOSEDDATETIME <> MakeDate(1900,1,1), DATE(FLOOR(NUM(CLOSEDDATETIME))) - DATE(FLOOR(NUM(REGISTRATIONDATETIME))),0 ) AS TURNAROUND

Id   RegisterDateTime    ClosedDateTime    turnAround

1     01-11-2012               10-11-2012               9

2     08-11-2012               16-11-2012               8

3     20-11-2012               01-01-1900  

The function for turnAround does work for the items that has a closedDateTime. But what i want is dat every record that has a ClosedDateTime of 01-01-1900 should go in the formula with the date of today. So in example of ID =3 the formula should be 26-11-2012 - 20-11-2012 is turnAround of 6 days

Anybody a idea?

Kind regards,

Jeroen

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Current expression:

IF (CLOSEDDATETIME <> MakeDate(1900,1,1), DATE(FLOOR(NUM(CLOSEDDATETIME))) - DATE(FLOOR(NUM(REGISTRATIONDATETIME))),0 ) AS TURNAROUND

Can't you just add this instead of the 0 in your current expression?

IF (CLOSEDDATETIME <> MakeDate(1900,1,1), DATE(FLOOR(NUM(CLOSEDDATETIME))) - DATE(FLOOR(NUM(REGISTRATIONDATETIME))),TODAY()-DATE(FLOOR(NUM(REGISTRATIONDATETIME))) ) AS TURNAROUND

View solution in original post

2 Replies
Anonymous
Not applicable
Author

Current expression:

IF (CLOSEDDATETIME <> MakeDate(1900,1,1), DATE(FLOOR(NUM(CLOSEDDATETIME))) - DATE(FLOOR(NUM(REGISTRATIONDATETIME))),0 ) AS TURNAROUND

Can't you just add this instead of the 0 in your current expression?

IF (CLOSEDDATETIME <> MakeDate(1900,1,1), DATE(FLOOR(NUM(CLOSEDDATETIME))) - DATE(FLOOR(NUM(REGISTRATIONDATETIME))),TODAY()-DATE(FLOOR(NUM(REGISTRATIONDATETIME))) ) AS TURNAROUND

jagan
Partner - Champion III
Partner - Champion III

Hi,

Try this

IF (CLOSEDDATETIME = MakeDate(1900,1,1), Today(), CLOSEDDATETIME) -

DATE(FLOOR(NUM(REGISTRATIONDATETIME)))  AS TURNAROUND

Hope this helps you.

Regards,

Jagan.