Choosing all records where selected date is within a range

[#1]

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.

[#2]

He Rory,

What about this solution using calender objects.

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

[#3]

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.

[#4]

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

[#5]

Hi Jelco,

No calendar. Lowest date level is day.

Example with limited subset of data is attached.

Thanks!

[#6]

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

[#7]

Hi Marius,

Sorry - being a bit dim.

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

Thanks,

Rory.

[#8 · Accepted solution]

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.