Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Choosing all records where selected date is within a range

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.

1 Solution

Accepted Solutions
Not applicable
Author

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.

View solution in original post

7 Replies
Not applicable
Author

He Rory,

What about this solution using calender objects.

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

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

Hi Jelco,

No calendar. Lowest date level is day.

Example with limited subset of data is attached.

Thanks!

Not applicable
Author

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

Not applicable
Author

Hi Marius,

Sorry - being a bit dim.

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

Thanks,

Rory.

Not applicable
Author

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.