Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.

How do i instruct formula tool to take column based on row value?

6 - Meteoroid

Hi.. I am trying to build a formula for CAGR where numerator would be same but denominator depends on column "Data Availability"



For instance, In the picture, If I have to calculate Company CAGR for First Row, I would pick Data availability column for taking column.


The formula i am using restricts it to Year T - Year T 4

i want to make it dynamic s that it picks all years given in Data Availabilty and according CAGR year gets updated for the company.


Formula :

if [Data Availability] = "N/A" then Null()
elseif [Data Availability]= "Year_T-3" then
Pow([Year_T]/[Year_T-3],(1/[Market Growth CAGR Period (years)]))-1
elseif [Data Availability]= "Year_T-2" then
else Pow([Year_T]/[Year_T-1],(1/1))-1


The ones highlighted should be dynamically picked for n number of years based on column given in Data availability column.


Any idea how this is possible?

Alteryx Certified Partner
Alteryx Certified Partner

@Priyas_4525 - could you give us an equivalent dummy dataset (retaining the column names and structure) for us to look at?

Alteryx Certified Partner

I would suggest trying the Transform > Transpose tool. Select the Year_ fields as the data columns and everything else as Key columns. This will put the 'Year_' column headers in a new field ('Name') and the data in a new column ('Value').


Your formula could then be some thing like:


if [Data Availability] = "N/A" then Null()
elseif [Data Availability]= [Name] then
Pow([Year_T]/[Value],(1/[Market Growth CAGR Period (years)]))-1
else 0


You could then use the Crosstab tool to restore the Year_ rows to columns. You might then need to filter unneeded rows or summarize the new Company CAGR field.