Dear All
I have a chart where I want to show the expression only when the record is valid according to a user-selected date. For example, if the user wants to see what the position was up to yesterday, I want the totals shown to be those excluding today. I have several records for each ID and in addition to selecting only those that are valid up to and including the selected date I want to choose the maximum record that meets that criteria. The data comes from a table where status changes and their dates are recorded and I want to be able to recreate the position on a particular date. So if someone has 3 records dated 1/11/14 with status C/, 1/12/14 with status C/F and then 15/8/15 with status U/F I want to be able to access the record with the max date based on the user-selected date. So if the user selected 1/1/15 the record included in the expression calculation would be the one with C/F status. Then if I want to see what the position was for 1/9/15 I would get the record with the U/F status.
Something like: select max date where date <= today (if the user selected today).
I have tried already and can do it for 'today' but the users want to see the position in historic terms as well - for example last week, week before etc.
Here is an example of what I have done so far. I have a master calendar for the user to select from.
Any help would be really appreciated.
