Using Variable to call Field Name based on ListBox Selection

[#1]

Greetings,

          I am fairly new to Qlikview and am having an issue with a business requirement. I have found a lot of great information through this community but can't seem to work out this one.

I have a listbox with years, 2012, 2013, 2014, 2015, 2016 from field [Year]

I have a straight table that displays sales targets for several products one year at a time.

Based on the year selected, I want to use the Field with the targets for that particular year

I have the below Variables

vYear = GetFieldSelections(Year)

vCrushing =if(WildMatch(vYear, '*2016*', CrushingTGT2016, if(WildMatch(vYear, '*2015*', CrushingTGT2015, if(WildMatch(vYear,         '*2014*', CrushingTGT2014, if(WildMatch(vYear, '*2013*', CrushingTGT2013, if(WildMatch(vYear, '*2012*', CrushingTGT2012, 'OTHER')))))

The formula on the straight table is =(sum({$<YTD_Flag =>} $(vCrushing/12))*Num(max(Month))

Each of the TGT values are separate fields on an Excel spreadsheet

The idea is that when a Year is selected, the variable vCrushing will set the correct field in the formula thus displaying targets for specified year.

The formula works fine if I call the field directly but when introducing the variable to the mix it no longer works. Can a variable be used to call a field name and then, use that field name in a formula dynamically?

[swuehl]

I think you need to put the division by 12 outside the dollar sign expansion of the variable

=(sum({$<YTD_Flag =>} $(vCrushing)/12)*Num(max(Month))

edit:

Also double check e.g. by leaving the expression label empty and then inspecting the expression header in the table, that the expression with the dollar sign expansion executed looks exactely like your original expression without variable

[MarcoWedel]

Hi,

maybe you should reconsider your data model as well.

Try crosstable loading your CrushingTGT* fields to get the actual CrushingTGT values and a CrushingTGTYear field.

This might simplify your application and render your vCrushing variable obsolete.

hope this helps

regards

Marco