Dynamic set analysis dimension

[triekong]

i have 3 Fiscal year flag fields in my dataset, for 1 Amount Field. 'FiscalYear1Amount', 'FiscalYear2Amount' and 'FiscalYear3Amount' respectively. when 'FiscalYear1Amount'=1, it filters the 'Amount' field to only the amount for fiscal year 1. and so forth.

My current sum of Amount for the current fiscal year is: sum({<FiscalYear1Amount={'1'}>} amount).

i want to be able to change the dimension dynamically, to maybe FiscalYear2Amount or FiscalYear3Amount, using a variable $(v_FiscalYear), which should pass the field for the selected fiscal year.. 

sum({<$(v_FiscalYear)={'1'}>} amount)

 but my syntax seems off. please guide.thanks!

(SN: All the fiscal year flag fields have values of either 1 or 0)

[sunny_talwar · Accepted solution]

Try one of these

Sum({<$(='[' & $(v_FiscalYear) & ']') = {'1'}>} amount)

or

Sum({<$(='[' & v_FiscalYear & ']') = {'1'}>} amount)

Ignore the fact that syntax editor doesn't like the expression... just click okay to see if you get the correct result or not

[triekong]

thanks @sunny_talwar ! big help

Quick note to anyone else, when passing the variable, make sure the field name is in single quotes. ''

for example: when i pass FiscalYear1Amount, i make sure i include it as 'FiscalYear1Amount'