Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
sasqliksense
New Contributor III

need to get the count of records based on sysdate-lastactivitydate>4 hours

Hi All,

i have records in my qvd as follows

id                     activitydate

---                   -----------------

1                    12-jun-2016 14:20:30

2                    05-sep-2017 10:00:03

3                    05-sep-2017 11:00:03

4                    04-sep-2017 10:00:03

5                    03-sep-2017 10:00:03

6                    03-sep-2017 10:00:03

7                    02-sep-2017 10:00:03

Now i need meassure which gives the count of records based on condition (sysdate-activitydate> 4 hours).

i tried to create like this but didn't work..

count({<[interval(activitydate-now(),'HH')={'>4'}]>}  id).

please help any one on this scenario

1 Solution

Accepted Solutions

Re: need to get the count of records based on sysdate-lastactivitydate>4 hours

Try this

Count( if( Interval( activitydate-now(),'hh:mm' ) > 4  ,id))

5 Replies

Re: need to get the count of records based on sysdate-lastactivitydate>4 hours

Try this

Count( if( Interval( activitydate-now(),'hh:mm' ) > 4  ,id))

sasqliksense
New Contributor III

Re: need to get the count of records based on sysdate-lastactivitydate>4 hours

Thanks Anand.

If i want to add one more condition in count.like i need to add status=2 and sysdate-activytydate>4hours.

How can i use?

sasqliksense
New Contributor III

Re: need to get the count of records based on sysdate-lastactivitydate>4 hours

Thanks Anand,

I can add multiple conditions..

alaope2q
New Contributor II

Re: need to get the count of records based on sysdate-lastactivitydate>4 hours

Count( if(status=2 and ((Interval( activitydate-now(),'hh:mm' )) > 4  ,id))


if you want to count distinct records, use


Count (Distinct( if(status=2 and ((Interval( activitydate-now(),'hh:mm' )) > 4  ,id)))

Re: need to get the count of records based on sysdate-lastactivitydate>4 hours

Yes sure this way

Count( if( Interval( activitydate-now(),'hh:mm' ) > 4  and status=2 ,id))


Or


Count( if( status=2 and Interval( activitydate-now(),'hh:mm' ) > 4  ,id))

Community Browser