What's the equivalent of the excel $ sign for anchoring to a reference cell?

[mattd17]

Still learning ins and outs for Alteryx. As mentioned in the subject line, looking to replicate a very simple and widely used excel function.

I have a time series for multiple columns, with each row as a value on a monthly basis. Basically, like the below - but I want to calc a new field for each region based off the Jan 2000 row as a reference. What's the best way to do this when I have hundreds of rows and hundreds of columns?

Month	Region A	Region B	A calc	B calc
Jan 2000	100	100
Feb 2000	101	102
Mar 2000	102	104
Apr 2000	103	106

Thanks,

Matt

[princejindal]

Hi @mattd17,

You can simply create a new column using formula tool with Jan 2000 value in each row of that column. Then you can easily refer to that amount.

You can also declare global variables under the constants pane in the workflow tab of the configuration window. This variable can be used anywhere in the workflow.

Hope this helps!

Cheers!

[Atabarezz]

Did you check the Running Total tool?

https://help.alteryx.com/current/RunningTotal.htm?Highlight=running%20total

You can calculate a cumulative sum easily...

[danilang · Accepted solution]

Hi @mattd17 

As @Atabarezz and @princejindal imply but never actually state, there is no equivalent of anchoring a cell in Alteryx.  All the data that you will use must be available either on 

1. the current row or

2. a group of surrounding rows if you're using a Multi-row tool.  But even here, all references are relative.  You can't specify that you need the value from the 1st row.  You can only reference the current row +/- N rows.  

Any summaries, totals averages, min values, etc. that you need, must be calculated separately and joined back to data set rows before performing your row-level calculations

In your case, the data set is very wide and alteryx can only act on a single column at a time using the formula tool.  To get around this and avoid having either hundreds of formula tools or one tool with hundreds of formulas, the standard method is to transpose them so that all values are in 2 columns that represent the name of data your working on and the value associated with it.  Perform your calculations on those 2 columns and then Crosstab back to get your data in the original form.

I've added a couple of columns to your input dataset to illustrate the concept

Here a workflow that gives you what your looking for.  

The bottom stream is the Transpose-Calculate-Crosstab process.  The top stream with the final join simply provides the data in the original wide format.  At the start of the bottom stream, the filter and join tools serve to add you initial column values onto each data row.  

If you follow the results as they pass though each tool, you'll get an understanding of the process

Have fun!

Dan

[mattd17]

@danilang This is pretty much exactly the workflow I was hoping someone would provide. I had a suspicion that transpose/crosstab would be used somehow but couldn't figure out something relatively simple to do with it. Thank you!