# Alteryx Designer Discussions

Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here
SOLVED

## Mathematical transformations in a table

Highlighted
5 - Atom

Hello All,

I am new to Alteryx and have been trying to figure out how to do basic mathematical functions (percentages and additions) in a table.The original table is as follows:

 Monday Tuesday Sent 150 100 Undelivered 20 15 Delivered but not opened 90 50 Opened 25 10 Clicked 15 25

Following are the transformations I want to carry out and then get the output:

1. Instead of the "undelivered" row, I want it to say "Delivered" where Delivered = Sent - Undelivered

2. Add a % column in front of each Monday and Tuesday fields and calculate percentages based on the following formulas:

• % delivered = (Delivered / Sent) *100
• % delivered but not opened = (Delivered but not opened/Delivered)*100
• % opened = (Opened/Delivered)*100
• % clicked = (Clicked/Delivered)*100

Hence after this, the output should look like the follows:

 Monday % Monday Tuesday % Tuesday Sent 150 100 Delivered 130 86.7% 85 85.0% Delivered but not opened 90 69.2% 50 58.8% Opened 25 19.2% 10 11.8% Clicked 15 11.5% 25 29.4%

I have been struggling with cross-tabs and summarize tools to do this transformation. I am attaching the excel for the same (the input file).

In case you might be thinking why not do it on Excel, I have thousands of similar tables with me and this is the only format in which it is given to me so Alteryx is the only way to analyze it.

It will be great if all amazing people of this community can help me with the workflow.

Highlighted
Alteryx

I have attached a workflow which produces the solution you are looking for below, it is a bit of a long workflow to produce the results and I'm sure it could probably be done in fewer steps! As you noted below, it's the combination of a number of cross-tabs and transposes to get to the result.

Let me know if you have any questions on the workflow, or if this works for you.

Thanks

Will

Highlighted
5 - Atom

Hi @wdavis,

This is really helpful, thanks a ton! This is exactly what I was looking for.

One small further help, in the output you have shared can you exchange row 1 (Clicked) and row 5 (Sent). Sorry for the repeated trivial request.

Highlighted
Alteryx

I have updated the workflow to allow you to reorder the data. Unfortunately it is a bit of a manual process if you are looking to replicate, where I have defined the order using IF statements within the formula tool to create that order.

Let me know if this works for you!

Thanks

Will

Labels