Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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
jsn
Honored Contributor

Re: Count Num of days

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

2 Replies
jsn
Honored Contributor

Re: Count Num of days

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

MVP
MVP

Re: Count Num of days

Hi,

Try this

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

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

Hope this helps you.

Regards,

Jagan.

Community Browser