topic Re: calculating date difference in QlikView

calculating date difference

Mon, 16 Jun 2014 10:09:55 GMT

i am trying to calculate the difference from the last modified date to present date..(today) i have attach an image of the data table that i am using.. as an output i need to take a count of all the incidents that is in the "pending " state more than 10 days.. can some one plz help me with this really really would appreciate the help

thank you in advance

Re: calculating date difference

MK_QSL — Mon, 16 Jun 2014 10:13:47 GMT

You can create a flag in your script and use SUM(Flag)

IF(Status = 'Pending' and Interval(Today() - [Modified Date],'DD') >= 10, 1, 0) as Flag

Re: calculating date difference

Mon, 16 Jun 2014 10:16:02 GMT

=if((today()-LastModifiedDate)>10 and (status = 'pending'),1,0)

Re: calculating date difference

Mon, 16 Jun 2014 10:21:18 GMT

hi sneha

my today date n the last modified date formats r different so will this function work??

Re: calculating date difference

Mon, 16 Jun 2014 10:23:55 GMT

hi manish

my today date n the last modified date formats r different so will this function work??

Re: Re: calculating date difference

Mon, 16 Jun 2014 10:27:28 GMT

convert both dates to same format for comparision purpose

=if((timestamp(today())-timestamp(LastModifiedDate)>10 and status = pending),1,0)

Re: calculating date difference

jagannalla — Mon, 16 Jun 2014 10:29:58 GMT

Try to convert your date column into date format and then use the expression.

Re: calculating date difference

Mon, 16 Jun 2014 10:37:06 GMT

i converted my date column in to the right format but i am still unable to get a count 😕

Re: calculating date difference

jagannalla — Mon, 16 Jun 2014 10:42:53 GMT

Can you attach sample file.

Re: calculating date difference

Mon, 16 Jun 2014 10:51:25 GMT

i am sorry Jagan i can not attach the file due to official reasons but i have attached an image of the data table in that i have converted my last modified date in to the proper format... i need to take a count of all the incidents which is in the in progress state more than 10 days..

=if((today()-SMod_date)>10

this function might be right but how can i take a count ?? 😕

Re: calculating date difference

PradeepReddy — Mon, 16 Jun 2014 10:54:21 GMT

try this..

Count(IF((today()-SMod_date)>10 and status='pending',IncidentID)

Re: calculating date difference

MK_QSL — Mon, 16 Jun 2014 10:55:42 GMT

Can you provide your sample data in excel file?

Re: calculating date difference

Mon, 16 Jun 2014 10:59:41 GMT

In the backend create a new field if((Today() - SMod_Date)>10,1,0) AS NoOfDays

Use

SUM({$<status={'pending'}>}NoOfDays)

Re: calculating date difference

SunilChauhan — Mon, 16 Jun 2014 11:03:33 GMT

count( distinct if(Status='In Progress' and interval(today(),S_Mod_Date,'DD')>10,[Incident ID])

hope this helps

Re: calculating date difference

jagannalla — Mon, 16 Jun 2014 11:05:31 GMT

=Count(If(Interval(Today()-S_Mod_Date,'D')>10 and Status='In Progress',IncidentID,Null()))