Use dynamic field names in a formula

[siddharthpotlia]

Hi all,

I am facing a challenge with developing a dynamic formula, over here what I want to do with the below table is:

Benefit Start (YYYY)	Client Initiative Start (YYYY)	Benefit Percentage	Selected Codes	123	112
2021	2021	2	123,456	2	0
2022	2021	1	123,112

1. Check if Value in Column 1 = Value in column 2, If not return 0 value

2. If first condition is met check if the code (123 here) is present in the column "Selected codes or not" if yes then return the value present in Benefit Percentage, if not present return zero value

One caveat here is the codes (123, 112 here) will keep on changing hence I will be needing a dynamic formula.

Essentially we need to check if a particular code is in selected codes, and is selected for the same year. Then what will be the benefit percentage achieved. 

If either of the two conditions is not met return zero value:

1. Years not matching

2. Code not present under selected codes cell

And if both are met we need the value in benefit percentage 

I have been struggling with this for a very long time, would really appreciate if anyone could help out with this.

[Qiu]

@siddharthpotlia 
Unless I am missing something here, it should not be so difficult.

[siddharthpotlia]

Hi Qui,

As mentioned the codes (here 123) will keep on changing, so we need the formula to use the field name (Which will be dynamic)

[atcodedog05]

Hi @siddharthpotlia 

Here is how you an do it.

Workflow:

1. Using transpose tool keeping info columns as key i am converting code column to rows. Here dynamic or unknown column is checked so if the code column name changes it will accommodated. I have created 2 scenario with different code columns for example.

2. Using select tool to convert value column to int datatype

3. In formula tool applying logic to check year1=year2 and is column present in selected code if yes [percentage] if not 0

4. Using crosstab tool to convert codes back into columns like below.

This will keep the column selected dynamic

Hope this helps 🙂