I'm trying to do an IntervalMatch but it doesn't seem to be making any associations as I would expect it to.
I've attached the QVW but it's quite complicated in other parts, so I've tried to isolate:
- the relevant part of the datastructure (by replicating the EndDate field but only bringing in those values that are numerical),
- the relevant area of the script (this is in a tab called "INTERVAL MATCH PROBLEM"),
- the relevant objects in the layout (a sheet called "INTERVAL MATCH PROBLEM").
My aims are as follows:
- Allow the user to either select a start and end point, OR select a series of months
- For a given start point (e.g. 1/1/10), the corresponding months should be automatically associated (e.g. Jan-10, Feb-10, ..., Dec-10, Jan-11, Feb-11... etc all the way up to the maximum month read in)
- For a given end point (e.g. 31/12/10), the corresponding months should be automatically associated (e.g. Dec-10, Nov-10, Oct-10, .... Jan-10, Dec-09... etc all the way down to the minimum month read in)
- Thus the combination of a single start point and a single end point would produce a selection of associated months (e.g. start 1/1/10 and end 31/3/10 would give the months Jan-10, Feb-10 and Mar-10)
- Vice versa, so (e.g.) selecting months Feb-10, Jun-10 and Jul-10 would give a start point of 1/2/10 and an end point of 31/7/10.
- So startpoint:month and endpoint:month should both be many:many relationships, but (combo of startpoint & endpoint):(month) should be a one:many relationship.
I want to do this because I am pulling data from a few different QVWs via QVDs, and different methods of date selection are used (and make sense) in the different contexts of the various QVWs. E.g. for Fund Performance data, users are used to selecting discrete months (to see, e.g., performance for Feb-10 and Feb-11), but for AUM data they are used to selecting a single start point and a single end point (to get a snapshot at the start and end, and a summary of flows in between the 2 points). In my new QVW (draft attached), I want to (for example) select start point = 1/1/10 and end point = 31/3/10, and see the AUM at these 2 points, the flows in AUM between these 2 points, and monthly discrete performance for Jan-10, Feb-10 and Mar-10. I want to be able to achieve the same result by instead selecting the 3 discrete months.
I would be very grateful if somebody could explain:
(1) Whether this is possible at all
(2) Whether the IntervalMatch function is the best way to achieve it
(3) Why my IntervalMatch function is not working (have I done the script wrong?).
To demonstrate the problem (3), see the table box on the 2nd sheet of the QVW.
Any help is much appreciated, thank you very much.
Sarah
