Dynamic Calculations

[CrayonZac]

Hi experts,

I have a question about Dynamitic Calculations. Please have a look at the following tables.

records	Dec-17	Dec-18	diff
1	100	200	100
2	300	410	110
3	124	988	864
records	Dec-18	Dec-19	diff
1	200	400	200
2	410	800	390
3	988	1000	12

The first table is the current one, and the next year, the field names are different. I have Dateformat to change the Names, and I was wondering is there someway to calculate diff next year, by (column 3 - column 2), rather than Dec-19 - Dec-18

Thanks a lot

[JohnJPS]

Yet another approach, but which requires field-by-field selection: Dynamic Select with a Formula specifying FieldNumber:

[CrayonZac]

Thanks again. I noticed that Dynamic Select can select columns by Field Number, however, I would like to use Field Number to calculate Last 12 Month Value,. Output after Dynamic Select still keep the original Field Names, which will get changed periodically.

[kmoreno]

Thanks, this was super helpful!

I've been trying to find an alternative to using the transpose tool because it slows down my workflow and isn't the most practical for large volume.

Do you have any examples of recreating the crosstab functionality in the R Tool?

[JohnJPS]

HI @kmoreno,

In the R tool, it's as simple as:

df1 <- read.Alteryx("#1", mode="data.frame")
df2 = as.data.frame(t(df1))
write.Alteryx(df1, 1)
write.Alteryx(df2, 2)

This reads the input into df1, generates df2 as the transpose of df1, and then sends df1 to output1 and df2 to output2.

This is a full transpose, so a bit less functionality that the CrossTab tool.

Hope that helps!

John