Valid from & Valid to Set Analysis see Example attached
I working on an application with multiple layers of information which besides the usual keys all have a valid from and valid to date. Each layer of information can change from one day to the other.
In order to be able to retrieve the correct set of information for one particular keydate i will have to check if the object is valid on that particular keydate and narrow down accordingly. This would have to be done across all related layers of data involved in the analysis to get the correct result.
I have looked at other options such as the intervals function, please do focus your answers on this particular way of getting the job done. Attached you will find an application demonstrating the problem with 1 layer of data, if this works i could replicate the set analysis across all other layers to get the correct result.
The aim is to enter a key date and for the report to show only objects which are valid on that particular key date, meaining valid from is larger or equal the key date and valid to is smaller or eqal the keydate. This should in theory provide the correct result. I could in this case run the same set analysis accross all Valid From and Valid To dates in all related tables and narrow down to just retrieve information that is Valid on the selected day.
Thanks for the response, I should have looked closer. I am having issues in the main application and after a long night over it I was convinced that this piece of set analysis does not work, but clearly it does, this at least eliminates that part of the set analysis being the problem. Tomorrow I will investigate further....
Are you guys aware of of any other, easier way, of linking that sort of data? Interval match based on a Day Calendar creates huge linking tables especially when the theoretical end date in the master data is 31.12.9999 and start date is 01.01.1900
If you're doing it one key date at a time, then the set analysis solution you used seems fine to me. The problem comes if, say, you want a chart showing the number of valid objects for EACH possible key date, using a KeyDate field instead of a vKeyDate variable. One solution would be to replace your set analysis with an if:
count(if(validto>=KeyDate and validfrom<=KeyDate,counter))
However, if your data set is large enough to cause problems with intervalmatch, it is likely large enough to cause performance problems with this sort of count(if()).
I'm not thinking of anything other than count(if()) or intervalmatch, though. It seems like you either need to connect a KeyDate to a date range in the expression, or in the script. If in the expression, and dealing with multiple key dates at once so you can't use set analysis, I think any solution is going to be very slow to execute. If in the script, I think any solution is going to create a large data structure. But I don't see other options.
If the set analysis isn't sufficient, I'd probably try the count(if()) approach next.