How to compare row values based on values from another field?

Hi Experts,

I am a new user in Alteryx and would like to seek advice from the community. This task should be simple but due to limited knowledge of Alteryx tools, I don't seem to be able to find solution for these..

Below is my question:

Question 1: I have a table as below screenshot and would like to fill in the value for Count, Expense, Avg_Expense of 'Diff', which is basically the difference in value of "T+1" minus "T". Some thread suggests Multi-Row Formula Tool but I'm not quite sure of how to use this tool and wonder if anyone would guide me on this..

Question 2: After filling in the values for 'Diff', I'd like to transpose the field into the second table below, which would be my end result.

I tried using transpose and crosstab but not able to get the result I want. Perhaps I am missing some other tweaks needed for this..

alteryx question.png

Any idea/suggestions is much appreciated! Thanks in advance!

Transformation

Apps

Preparation

Accepted answers

Here's another method that doesn't use multirow formulas.

1. I transpose the data first, then construct a header field.

2. Crosstab the data using the new header field.

3. Use a normal formula tool to calculate values.

4. Use a select tool to re-order the columns as required.

Regards,

Ben

All comments

Step 1 requires a multi-row formula tool.

Step 2 requires a cross-tab tool.

You should be good then.

Hi @wwatson ! Thanks for your reply. Would you be kind enough to demonstrate how to use Multi-Row Formula tool specifically for this case?

Hi @owenkosnen,

Below is the solution to your problem:

Comparing row Values.png

PFA workflow as well. I hope it helps.

New Workflow16.yxmd

Hi @owenkosnen ,

This is the simplest I can come up with:

1) Use of multi row formula wherever "difference" is required

2) Transpose to get the uniform format

3) Crosstab to get the desired result.

Hope it helps!

Thanks,

S.

Data Manipulation.yxmd

Here's another method that doesn't use multirow formulas.

1. I transpose the data first, then construct a header field.

2. Crosstab the data using the new header field.

3. Use a normal formula tool to calculate values.

4. Use a select tool to re-order the columns as required.

Regards,

Ben

Thanks a lot! this is very helpful.

Wow, now I know how to use multi-row formula a bit. Thanks for your help!

.

.

Quick Links

Tags

This months top contributors

AkimasaKajitani 390

michael_weaver 335