Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Multi-Row Formula Tool Question: Calculate Percent Of Total

ValeriaK
7 - Meteor

Hello Alteryx Community,

I'm seeking your expertise in using the Multi-Row Formula tool to achieve specific calculations.

Here's a simplified version of my task:

Objective: Calculate the percentage of "Flag 1" (Sub Category) out of the "Grand Total" (Flag 2) for each column (7, 14, 30, 60, 90, and 120 days).

Example: Input Data:

 

INPUT_1.jpg

MULTI_ROW_FORMULA.jpg

 

 

 

 

 

 

 

 

 

 

 

Desired Output:

OUTPUT_2.jpg

 

 

 

 

 

 

 

 

 

 

Your insights and suggestions are greatly appreciated.

Thank you!

9 REPLIES 9
OTrieger
12 - Quasar

@ValeriaK 
Have you tried the following formula:
Row +1 = Row 0/ Row -1?

I never tried it before, therefore asking if you tried it?
The thing is that you can do it only for one field at a time
So it will be better to  Transpose the data create the 3 columns and then flip it to rows.

OTrieger
12 - Quasar

Or you can do it in a batch macro one field at a time and at the end you will get the full table. But it will require more transformation so each time the automation will take only one field. 

ValeriaK
7 - Meteor

@OTrieger Thanks for the solution ideas.
I'm having a bit of trouble testing the formula you mentioned because I've not used this tool for calculation purposes before.
I'm not sure how to go about testing the formula myself.
I've attached a mockup of the data I'm working with (initial post)
Would you be able to help me out with this? I'd really appreciate it!
Thanks!

binuacs
21 - Polaris

@ValeriaK one way of doing this

image.png

apathetichell
19 - Altair

Try:

Transpose. use flag as a key.

use multi-row formula -> update value - group by [Name]

 

I used: 

if contains([Flag],"Total") then [Value]-[Row+1:Value] else [Value] endif - which assumes your FIRST row is your total.

 

cross tab - flag is your key field/ name  is your columns - value is your rows - > IMPORTANT set it to percent column.

 

you now have your percents - so you have to do some things to clean it.

 

1) use a formula tool to set your flag to "% column" or whatever

2) use a formula tool to set percent as you want.

 

use a sample tool - set it for 1. don't group.

 

connect dynamic rename

select all fields.

replace([_CurrentField_],"XCol_","")

 

use a union tool.

 

ValeriaK
7 - Meteor

Good morning, Alteryx Gurus!

Thank you all for your fantastic suggestions!

Shoutout to @binuacs!🙏

Your solution works great for my needs, and I really appreciate the time you took to prepare the demo.

Have a great day, everyone!

ValeriaK
7 - Meteor

@apathetichell 👋

Thank you for your help!
Your suggestion is another great solution and I'm really keen to keep learning.

There's just one thing I'm still struggling with.

Would it be possible to add a demo to the actual workflow?

I think that would really help me get my head around the whole process.

I'm really passionate about learning this stuff, and I know that having a visual representation of the workflow would make a huge difference.

Thank you in advance! 😊

apathetichell
19 - Altair

something like this - but @binuacs solution is just as good.

ValeriaK
7 - Meteor

@apathetichell 

Thanks a bunch for sharing your idea!🙏

I'm always excited to learn new ways to tackle tasks.

Saving it to my knowledge library.

I really appreciate you taking the time to share your thoughts.

Have a fantastic rest of your day!
Posting all solutions combined.

Labels
Top Solution Authors