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

[owenkosnen]

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..

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

[wwatson]

Step 1 requires a multi-row formula tool.

Step 2 requires a cross-tab tool.

You should be good then.

[owenkosnen]

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

[grazitti_sapna]

Hi @owenkosnen,

Below is the solution to your problem:

 PFA workflow as well. I hope it helps. 

[szade1]

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.

[Ben_H · Accepted solution]

Hi @owenkosnen 

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

[owenkosnen]

Thanks a lot! this is very helpful.

[owenkosnen]

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

[nareshns8]

.

[nareshns8]

.