Calculations based on the maximum of another multi-select field
Hi!
I'm trying to understand how to solve a specific problem. We have a multi-select box where the user selects year/month in the format 200902 for Feb-2009. The user can select several consecutive months.
When this is done, I would like to present a value in a chart containing a sum of values from the start of the data set to the maximum date selected. If I, say, select 200910, 200911 and 200912 and the data set starts at 200601 I would like to calculate sum(value) for all data points 2006-01-01 to 2009-12-31.
If I create a text box with the following expression: =MonthsEnd(1, Left(max({$} TRANS_MONTH),4) & '-' & mid(max({$} TRANS_MONTH),5,2) & '-01') I can get the maximum date selected in the format "2009-12-31", but when I try to plug this into the calculation expression like this:
I get no result. If I replace my date calculation with a static date, it works fine. I know this solution will be wrong if the user selects a range with "holes" in it, but it will have to do. Any help is appreciated!
I solved this temporarily by having a read-only input box that calculated the max date as a variable, and then used that in the sum(). Is it possible to skip this step?
