Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Days pending from date with time

Dear Friends

I have field creation date coming from sharepoint. The date is captured in below format.

7/15/2014 3:01 PM

I have tried several formulas to subract it from today() in the script. But it is not working.

Please suggest the way around.

Thanks

Rahul

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Hi,

Something like below,

LOAD

  *,

  Interval(Now()-ISSUE.Created, 'hh') as Hours_Pending,

  Interval(Now()-ISSUE.Created, 'DD') as Days_Pending

;

LOAD

  ISSUE.ID,

  Timestamp(Date#(ISSUE.Created, 'M/D/YYYY h:mm TT')) as ISSUE.Created

;

LOAD * INLINE [

    ISSUE.ID, ISSUE.Created

    82, 7/15/2014 2:49 PM

    83, 7/15/2014 2:51 PM

    84, 7/15/2014 2:53 PM

    85, 7/15/2014 2:55 PM

    86, 7/15/2014 2:57 PM

    87, 7/15/2014 3:00 PM

    88, 7/15/2014 3:01 PM

    89, 7/15/2014 3:03 PM

    97, 7/18/2014 10:51 AM

    103, 7/21/2014 3:24 PM

];

View solution in original post

11 Replies
MK_QSL
MVP
MVP

Convert your Date Field as Below

TimeStamp(Alt(
TimeStamp#(Date,'M/DD/YYYY hh:mm TT'),
Timestamp#(Date,'M/D/YYYY h:mm TT')
))
as Date

Now use below to find the difference in hour..

=TimeStamp(Now()-Date,'hh')

Not applicable
Author

Hi Manish

Thanks for your email.

I have enter the formula as suggested

TimeStamp(Alt(
 
TimeStamp#([Created  ],'M/DD/YYYY hh:mm TT'),
 
Timestamp#([Created  ],'M/D/YYYY h:mm TT')
))
as Creation_Date,

 
TimeStamp(now()-(TimeStamp(Alt(
 
TimeStamp#([Created  ],'M/DD/YYYY hh:mm TT'),
 
Timestamp#([Created  ],'M/D/YYYY h:mm TT')
))),'dd')
as pendingdays

However it is missing two days in the pending days

ISSUE.Creation_DateISSUE.pendingdays
15/07/2014
  15:03:00
13
18/07/2014
  10:51:00
11
21/07/2014
  15:24:00
07
MK_QSL
MVP
MVP

Provide us your sample data file or apps..

Not applicable
Author

Hi Manish

Enclosed is the sample data file, it contains the ID and creation date.

I need to get how many days it is pending

so (today-creation date) as pending days

Thanks

Rahul

Anonymous
Not applicable
Author

Hi,

Something like below,

LOAD

  *,

  Interval(Now()-ISSUE.Created, 'hh') as Hours_Pending,

  Interval(Now()-ISSUE.Created, 'DD') as Days_Pending

;

LOAD

  ISSUE.ID,

  Timestamp(Date#(ISSUE.Created, 'M/D/YYYY h:mm TT')) as ISSUE.Created

;

LOAD * INLINE [

    ISSUE.ID, ISSUE.Created

    82, 7/15/2014 2:49 PM

    83, 7/15/2014 2:51 PM

    84, 7/15/2014 2:53 PM

    85, 7/15/2014 2:55 PM

    86, 7/15/2014 2:57 PM

    87, 7/15/2014 3:00 PM

    88, 7/15/2014 3:01 PM

    89, 7/15/2014 3:03 PM

    97, 7/18/2014 10:51 AM

    103, 7/21/2014 3:24 PM

];

MK_QSL
MVP
MVP

LOAD ID,

     Date as Date1,

     TimeStamp(Alt(

  TimeStamp#(Date,'M/DD/YYYY hh:mm TT'),

  Timestamp#(Date,'M/D/YYYY h:mm TT')

  )) as Date,

  Interval(NOW()-(TimeStamp(Alt(

  TimeStamp#(Date,'M/DD/YYYY hh:mm TT'),

  Timestamp#(Date,'M/D/YYYY h:mm TT')

  ))),'dd') as DifferenceinDays,

  Interval(NOW()-(TimeStamp(Alt(

  TimeStamp#(Date,'M/DD/YYYY hh:mm TT'),

  Timestamp#(Date,'M/D/YYYY h:mm TT')

  ))),'hh') as DifferenceinHours,

  Interval(NOW()-(TimeStamp(Alt(

  TimeStamp#(Date,'M/DD/YYYY hh:mm TT'),

  Timestamp#(Date,'M/D/YYYY h:mm TT')

  ))),'mm') as DifferenceinMinute

FROM TABLENAME

Not applicable
Author

Thanks Satyadev for your help.

Not applicable
Author

Thanks Manish for your help

Not applicable
Author

Dear Satyadev

I have to change the data source from list to XML that changed the date format.

Now it is coming as YYYY-MM-DD

I tried Interval(Now()-(Timestamp(Date#(ISSUE.Created, 'YYYY-MM-DD h:mm TT'))), 'DD') as Days_Pending

But it's not working, Do you know why?

2014-07-15
  14:49:23
2014-07-15 14:51:29
2014-07-15 14:53:49
2014-07-15 14:55:35
2014-07-15 14:57:24
2014-07-15 15:00:09
2014-07-15 15:01:55
2014-07-15 15:03:20
2014-07-18 10:51:51
2014-07-21 15:24:08
2014-07-22 08:40:20
2014-07-22 08:44:49
2014-07-22 08:48:11
2014-07-22 08:52:04
2014-07-22 09:00:19
2014-07-22 09:01:51
2014-07-22 09:06:59
2014-07-22 09:09:30
2014-07-22 09:12:19
2014-07-22 09:13:53