Qlikview to use dynamic created field names in an expression

[andriesb]

I am looking for the syntax to create and use a dynamic created field name in an expression. 

In a chart I have 13 periods and according to a period I would like to show the appropriate value for this period :

Result would be like this example :  The first 3 period does show the correct value :

In the expression it should be like  

=('Norm' & Periode)   but this syntax does not work

The dynamic value should be created  according to the appropriate periode:

If(Periode = '01', Norm01,
If(Periode = '02', Norm02,
If(Periode = '03', Norm03,
'')))

 Does anyone know how to use this in a shorter syntax ?

It's only to be used within one chart. 

[edwin]

a simple calculated dimension is =Pick(Match(Periode,'01','02','03'), Norm01,Norm02,Norm03)

[edwin]

but if it is in the expression, it needs to be an aggregation like 
=Pick(Match(Periode,'01','02','03'), sum(Norm01),sum(Norm02),sum(Norm03))

[andriesb]

Thanks Edwin !

Your Pick() did the trick, but also hoped there would be a more generic solution to dynamic create the field name to be used ;)) 

Up till now , that patch failed... 

[edwin]

not sure i understand when you say the pick function did the trick but the patch failed.  which patch is this?

[andriesb]

I hoped there was a more generic way of defining a field name to be read : e.g.

=$[('Norm' & Periode)]    - that would read the value of field Norm01 for the first Period 01

if that was possible, that would be much shorter and easier to use. 

I did use the suggested 'Pick' statement for now.   Thanks Edwin !

[marcus_sommer]

If you want to remain by your data-structure it couldn't be easier as the suggestion from Edwin. There is no way that anyhow created strings are treated as valid fields unless using a $-sign expansion. But this creates an adhoc-variable which is always calculated globally and before the chart is calculated and then applied to each call. This means you couldn't use it on a dimensional level.

My suggestion ist to change the datamodel and to put all Norm## fields into a single field. This could be done quite easily with The Crosstable Load - Qlik Community - 1468083.

- Marcus