topic button to select based on expression involving 2 fields and a limit in QlikView

button to select based on expression involving 2 fields and a limit

Thu, 15 Oct 2015 08:59:43 GMT

Please can someone assist?

I have an event table with 2 date fields – dStart and dEnd. I want to select all the records where dEnd-dStart >180 (i.e. events that took longer than 180 days) in a way that high-level users can access simply (like a bookmark).

I don't really want to create an extra ‘claim duration’ field at the outset (I know this would be an answer, but I'm trying not to bloat the field list too much as this confuses the users). I’ve been looking into adding a button as that gives me the ability to set up a whole ‘report scenario’ including the duration limitation.

The ‘Select in Field’ action lets me define an expression:

=interval(dEnd-dStart,'D')

But a Search String:

>180

Apparently does nothing. Is this just a syntax issue, or is there a fundamental reason why this doesn't work? I've been trawling the discussions and can't find a match for this scenario...

Thanks for any help you can give!

Re: button to select based on expression involving 2 fields and a limit

settu_periasamy — Thu, 15 Oct 2015 09:32:09 GMT

Hi,

Did you try to use the '$' Symbol (if dEnd and dStart is a variable), Like

=interval($(dEnd)-$(dStart),'D')

=interval(num(dEnd)-num(dStart),'D')

or Directly you will get difference value, if it is a date format..

=num(dEnd-dStart)

Re: button to select based on expression involving 2 fields and a limit

Thu, 15 Oct 2015 10:10:34 GMT

Hi. Thanks for the input, but I got the answer from a colleague in the end. For the benefit of anyone else who's a bit of an amateur like me :-), I'll explain...

I was thinking of the FIELD as being the data I was comparing (one or both of the dates), as opposed to the unique data I could use to identify the records I wanted to select. The solution was simply a Button with a Select in Field action as follows:

FIELD: event_id

SEARCH STRING: ='=IF(interval(dEnd-dStart,"D")>180,event_id)'

Sorted. Regards to all!