Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How do I determine how many days have past since a change?

I have a relational model that is all based on ITMSNumber. What I'm trying to do is something like the following. (Please excuse the pseudo code).

There is a field called ChangeDate and another field called ChangeNewValue.

Left keep

Table6:

Load

ITMSNumber

IF(ChangeNewValue='Quarantined- DSC',

THEN DaysPassed = Today() - ChangeDate)

SQL SELECT ITMSNumber,

ChangeNewValue,

ChangeDate

FROM MPL.dbo."tblCHANGE";

The date field (ChangeDate) is formatted like so- 2016-12-12 10:50:38.377

I want it to say that it's been 1, 5, 15, etc. days passed since the ITMSNumber has changed to 'Quarantined- DSC'.

I should say that I want a table that has a bunch of values in it for all of the ITMSNumber's that have changed to Quarantined- DSC. Like so-

ITMSNumber | DaysPassed

12374              2

12407              10

18472              8

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Hi,

Replace

IF(ChangeNewValue='Quarantined- DSC',

THEN DaysPassed = Today() - ChangeDate)

with

IF(ChangeNewValue='Quarantined- DSC', date(Today()) -floor(date(TIMESTAMP# (ChangeDate,'YYYY-MM-DD hh:mm:ss.fff') as DaysPassed

Best regards,

Cosmina

View solution in original post

10 Replies
Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

Left keep

Table6:

Load

ITMSNumber

IF(ChangeNewValue='Quarantined- DSC', Floor(Today() - Date#(ChangeDate, 'YYYY-MM-DD hh:mm:ss.fff'))) as DaysPassed

;

SQL SELECT ITMSNumber,

ChangeNewValue,

ChangeDate

FROM MPL.dbo."tblCHANGE";

Help users find answers! Don't forget to mark a solution that worked for you!
Anonymous
Not applicable
Author

Hi,

Replace

IF(ChangeNewValue='Quarantined- DSC',

THEN DaysPassed = Today() - ChangeDate)

with

IF(ChangeNewValue='Quarantined- DSC', date(Today()) -floor(date(TIMESTAMP# (ChangeDate,'YYYY-MM-DD hh:mm:ss.fff') as DaysPassed

Best regards,

Cosmina

Not applicable
Author

There's supposed to be 4 closing parenthesis there just to note.

It didn't work. It didn't provide any values at all for DaysPassed

Not applicable
Author

This worked and seemed like it would be the right answer, but when I cross checked it with the database this is what I got.

Today is 2017-01-18

The date of the change was- 2017-01-03 09:28:44.763

The difference is 15 days, however, the DaysPassed says that it's only been 9 days since the change.

The script works but it's not giving the right results...

Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

IF(ChangeNewValue='Quarantined- DSC', Floor(Today() - Date#(ChangeDate, 'YYYY-MM-DD hh:mm:ss.fff')))

Help users find answers! Don't forget to mark a solution that worked for you!
Not applicable
Author

I changed it to

IF(ChangeNewValue='Quarantined - DSC', date(Today()) - Floor(date(TIMESTAMP(ChangeDate, 'YYYY-MM-DD hh.mm.ss.fff')))) as DaysPassed;

And it's giving me more accurate numbers, but they're still not correct.

Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

When I do that, I have the good result :

date(Today()) - Floor(date(TIMESTAMP('2017-01-03 09:28:44.763', 'YYYY-MM-DD hh.mm.ss.fff')))

OR

Ceil(Today() - Date#('2017-01-03 09:28:44.763', 'YYYY-MM-DD hh:mm:ss.fff'))

Help users find answers! Don't forget to mark a solution that worked for you!
Not applicable
Author

How are you getting 15? Mine is saying 10. It's been 15 days since 2017-01-03

Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

Capture.PNG

Help users find answers! Don't forget to mark a solution that worked for you!