I need to filter on groups where the month in the Acct Orig Eff Date field equals the renewal month AND the year is the max year. So in the case above group id AAA and BBB each have the month of "07" (7 in the case of renewal month) AND the the year is 2013 - the max of the years. So AAA and BBB would NOT be included in the chart.
What is the most efficient way to filter on the above?
Re: Comparing two dimension to determine chart data
If you have access to the script on the backend, you can use a preceding load to create another field. It will contain GroupID itself but only for those records which do not have Year = max(Year) and Month = Renewal Month.
Check the code and the result below:
//If year is max and month is same as renewal month, field will be null, else it will be GroupID
if ( left(AcctOrigEffDate,4)=Year(today()) and mid(AcctOrigEffDate,5,2)=RenewalMth,