max graph date from variable period

[#1]

hi is there a way to use a variable date as a max period on a graph ?

e.g. my expression below gives the 'Final Total' for months where the max Month is the variable [vReportDate]

sum([vReportDate]=max(Month),GP_Wait_List_Plan)

But this doesnt work.

Any ideas?

[vgutkovsky]

Well your syntax won't work because you have a nested aggregation, plus it's not proper set analysis syntax. But something like this could work:

sum({<Month={"<=$(vReportDate)"}>}GP_Wait_List_Plan)

Regards,

[#3]

Hi Vlad, thnks for your help the expression worked a treat.

Im just wondering if you can also help with the following in regards to the expression.

The expression works fine but it causes an issue when the user selects a period form the 'Month / Year' list box.

Basically although i want my expression to provide a maximum period for Actual BUT if the user selects a period which falls before the maximum then it should use the earlier period.

E.g. the user has selected April - 09 - Jul -2009 from the list box. Therefore the graph maximum date should be Jul - 09. As you can see the expression has made the graph show max date Jul - 10 (data points in Red Box shouldn't be shown).

Maybe set analysis should be included in the dimension?

Any ideas?

[vgutkovsky]

I would recommend that you change this not in the expression, but in the variable. So put in something like this for the variable value:

=rangemin(
 date#('07/2009','MM/YYYY'),
 if(len(trim(GetFieldSelections(MonthYear)))>0,date(max(MonthYear)))
)

where 07/2009 is replaced by your manual maximum and MonthYear is replaced by the user date selection field.

Regards,