topic Re: Days pending from date with time in QlikView

Days pending from date with time

Tue, 29 Jul 2014 08:38:44 GMT

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

Re: Days pending from date with time

MK_QSL — Tue, 29 Jul 2014 08:47:40 GMT

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')

Re: Days pending from date with time

Tue, 29 Jul 2014 09:00:20 GMT

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_Date	ISSUE.pendingdays
15/07/2014 15:03:00	13
18/07/2014 10:51:00	11
21/07/2014 15:24:00	07

Re: Days pending from date with time

MK_QSL — Tue, 29 Jul 2014 09:02:32 GMT

Provide us your sample data file or apps..

Re: Re: Days pending from date with time

Tue, 29 Jul 2014 09:09:08 GMT

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

Re: Days pending from date with time

Anonymous — Tue, 29 Jul 2014 09:24:05 GMT

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

];

Re: Days pending from date with time

MK_QSL — Tue, 29 Jul 2014 09:37:03 GMT

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

Re: Days pending from date with time

Tue, 29 Jul 2014 10:52:48 GMT

Thanks Satyadev for your help.

Re: Days pending from date with time

Tue, 29 Jul 2014 10:53:03 GMT

Thanks Manish for your help

Re: Days pending from date with time

Tue, 29 Jul 2014 13:07:10 GMT

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

Re: Days pending from date with time

Anonymous — Tue, 29 Jul 2014 13:23:32 GMT

Hi,

So the date format changed to YYYY-MM-DD hh:mm:ss. Try below,

Interval(Now()-(Timestamp(Date#(ISSUE.Created, 'YYYY-MM-DD hh:mm:ss'))), 'DD') as Days_Pending

Re: Days pending from date with time

Tue, 29 Jul 2014 13:27:52 GMT

Thanks Sec were making the issue, now it is working again