Within my Alteryx workflow I need to calculate the difference between each numeric column of data (e.g. monthly data) and create "delta" columns that calculate that difference. Success of the solution depends upon both not needing to update the workflow (e.g. dynamic calculations) and the performance of the solution (not needing to transpose the data).My friends Thales & Esther (EstherB47) recommended transposing the data.
When dealing with this kind of situation, the best you could do is to transpose your columns into rows.Not me! I know how to avoid the transpose tool and how to dynamically calculate formulas. Try this yourself and don't use a Transpose Tool or Multi-Field Formula. Imagine hundreds of columns with thousand or more rows. Here's some simple input data:
This way you can work with Multi-Row Formula, that can be much more flexible in regards to what you're looking for.
So basically use Transpose Tool and you'll have a better situation to compare previous records against the current ones.
This is the desired output:
The solution looks like this:
Step 2: Use a FIELD INFO tool to get the metadata (names & types) of incoming data.
Step 3: Use a FILTER tool to remove any non-numeric fields. If the KEY is numeric, we're going to have a problem. You'd need to use a SAMPLE tool to skip N records (N is the number of key fields).
Step 4: Configure a FORMULA tool to create "Output Field" names for each of the new fields.
Step 5: Configure a MULTI-ROW Formula tool to create an expression of:
"[" + ToString([Name]) + "] - [" + ToString([Row-1:Name]) + "]"Step 6: Use a sample tool to remove the calculation of Delta from the first record.
Step 7: Configure the CReW Dynamic Formula tool.
This might make for a clever CReW macro addition. Is there a volunteer to prototype a generic macro for the community?