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?

Highlighted
6 - Meteoroid

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

Priyas_4525_0-1584641508445.png

 

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
Pow([Year_T]/[Year_T-2],(1/2))-1
else Pow([Year_T]/[Year_T-1],(1/1))-1
endif

 

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?

Highlighted
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?

Highlighted
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
endif

 

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.

Labels