topic Re: Re: Choosing all records where selected date is within a range in QlikView

Choosing all records where selected date is within a range

Tue, 12 Aug 2014 09:28:10 GMT

Hi All,

I have a set of records where each record has a VALIDFROM and VALIDTO date. My requirement is to be able to select a date from the UI and have all records where that date falls between VALIDFROM and VALIDTO displayed in a straight table.

Been trying with an input box, variable and onchange trigger actions to select field values on VALIDFROM and VALIDTO, but not working as I expect.

Any ideas?

Thanks,

Rory.

Re: Choosing all records where selected date is within a range

Tue, 12 Aug 2014 09:31:57 GMT

He Rory,

What about this solution using calender objects.

Let me know if you have any problems converting it to your app.

Re: Choosing all records where selected date is within a range

Tue, 12 Aug 2014 09:42:12 GMT

Hi Jelco,

That certainly gives me some pointers. but the example you've provided has one date in the datamodel (HistoricalDate) and the two variables created by the calendar are used in a 'greater than/less than setup'.

I have two date fields I need to select on (VALIDFROM and VALIDTO) and I only want to select one date. So my selected value needs to lie between VALIDFROM and VALIDTO:

ID VALIDFROM VALIDTO

A 01/01/2010 31/12/2010

B 01/04/2010 31/03/2011

C 01/01/2011 31/12/2011

If I select date 01/03/2010 then I only want record A displayed. Select 07/04/2010 then records A and B displayed. 23/02/2011; records B and C displayed.

Thanks,

Rory.

Re: Choosing all records where selected date is within a range

Tue, 12 Aug 2014 13:00:21 GMT

Check!

Do you have a calender in your script?

What is the lowest date aggregation in your model? (Day/Week/Month)

I would suggest you convert the valdifrom and validto too a datekey which you can use dynamicly in the GUI.

Is it posible to upload a snapshot of your datamodel (table viewer?. The script or qvw would even be better.

greets,

Jelco

Re: Re: Choosing all records where selected date is within a range

Tue, 12 Aug 2014 13:36:28 GMT

Hi Jelco,

No calendar. Lowest date level is day.

Example with limited subset of data is attached.

Thanks!

Re: Choosing all records where selected date is within a range

Tue, 12 Aug 2014 13:44:30 GMT

How about pulling VALIDFROM into a data island (separate table) and then make your selections on the new field.

And then in your table use if(NewField <= VALIDFROM, (do calculation)).

Regards,
Marius

Re: Choosing all records where selected date is within a range

Thu, 14 Aug 2014 13:36:19 GMT

Hi Marius,

Sorry - being a bit dim.

Can you please give me a worked example on what you mean?

Thanks,

Rory.

Re: Choosing all records where selected date is within a range

Wed, 17 Sep 2014 13:24:19 GMT

Hi All,

Just for the record, I found a solution for this, although I doubt it's the most elegant.

Using a calendar input box I am able to select a date.

I put this date into a variable (vDate).

Use this variable in a calculated dimension in my straight table. If ValidFrom <= vDate and ValidTo >= vDate then resolve to a 1, else a null.

Suppress null values on this dimension

I'm sure there are better ways, but this gives the me the result I'm looking for.

Rory.