Mathematical transformations in a table

[harshsaraf]

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.

[wdavis]

Hi @harshsaraf 

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

[harshsaraf]

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.

[wdavis · Accepted solution]

Hi @harshsaraf 

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