Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
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
satyadev_j
Specialist
Specialist

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

satyadev_j
Specialist
Specialist

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

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