I have created dual values for year and fiscal year representation (FY 10-11, 2010) and I evaluate my fiscal year from the dates using year(addmonths(mydate, -3)).
This works as expected until it comes to set expressions.
I need to show the previous financial year value and tried
sum({<FIS_YEAR = { $(FIS_YEAR-1) }>} Sales)
FIS_YEAR - 1 returns me the numeric value (2009) while the FIS_YEAR in the set expression looks to be 'FY 09-10' because I can see that year total using sum({<FIS_YEAR = { 'FY 09-10' }>} Sales)
I haven't found a function that will give me the string value for the calculated fiscal year back. text() just returns the year as string but not the dual text value.
If I understand your problem correctly, you have a natural year (4 digit integer) and a derived, formatted string (FY 10-11) representing your fiscal year. If you are going to use set operations on the fiscal year, you will likely save yourself a lot of extra coding by simply creating a third field: the fiscal year as a four-digit integer. Then use the existing formatted string while presenting the fiscal year data, but use the handy integer number when performing SET operations. I hope my suggestion is useful.
