Get value of a field dynamically

[jackm75]

I have a list of fields called Month01, Month02, Month03, etc... I'm trying to return the value of Month## dynamically by using another field value, CurveStartMonth, to indicate the month numbers. So, if the value of CurveStartMonth is 5, then  'Month' & Num(CurveStartMonth,'00')  returns the desired text in the expression field, Month05, but I need the value of Month05 to be returned.  How can this be done?  Thanks!

[JonnyPoole]

If CurveStartMonth is a field in the data model, then you can grab its value using the only() function  ( Only() returns the value if there is only 1 possible value) and concatenate  it to a 'Month0' string . This occurs within a dollar sign expansion $() .

= sum(  $(='Month0' & Only(CurveStartMonth)) )

Below you see the total for Month02 show in  white text box, because i have Month=2 selected.

[stigchel]

Use dollar sign expansion (see help), something like

GetFieldSelections([Month$(=Num(CurveStartMonth,'00'))])

[m_woolf]

If you store that string in a variable, then you can use:

$(Variable)

[jackm75]

Jonathan, CurveStartMonth is the name of another Expression field, it's not part of the data. So, this doesn't seem to work for me, but thanks.

[JonnyPoole]

NP - can you post a qvw sample ?   or at least the expression behind 'curvestartmonth' and what kind of object its used in ?

[jackm75]

Basically 'CurveStartMonth' is using two available table fields to determine how old a placement is in months:

(1,if(day(PlacementDate)>=20,floor(((monthstart(PlacementDate)-monthstart(LoadDate)))/30 )+1,floor(((monthstart(PlacementDate)-monthstart(LoadDate)))/30 )))

In my example, this formula might return 5 for a particular selection.  So, I need to get the value of the table field Month05 related to the selection.

[jackm75]

m woolf, I tried using a variable, but it seemed to return just the same text value as I'm getting currently rather than the actual value of the field.

[jackm75]

Peit, I've tried several variations of $ expansion with no luck.  I hadn't tried GetFieldSelections, but still not getting the expected value.

[stigchel]

Yes, I was under the impression that CurveStartMonth was a field not an expression. The problem here is that dollar sign expansion is written out before evaluation, one consequence being that the evaluation value does not change per dimension value. This does complicate it considerably unless there is but one value for all your dimensions, and guessing from your issue, it is not. There are solutions with dynamic expressions using pick(match and a constructed string of all valid expressions per dimension see e.g.

Re: Dynamic Expression