topic Re: Using Variable to call Field Name based on ListBox Selection in QlikView

Using Variable to call Field Name based on ListBox Selection

Thu, 24 Mar 2016 18:12:35 GMT

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?

Re: Using Variable to call Field Name based on ListBox Selection

swuehl — Thu, 24 Mar 2016 18:43:29 GMT

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

Re: Using Variable to call Field Name based on ListBox Selection

MarcoWedel — Fri, 25 Mar 2016 02:22:52 GMT

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