Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
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:

 

 MondayTuesday
Sent150100
Undelivered2015
Delivered but not opened9050
Opened2510
Clicked1525

 

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% MondayTuesday% Tuesday
Sent150 100 
Delivered  13086.7%8585.0%
Delivered but not opened9069.2%5058.8%
Opened2519.2%1011.8%
Clicked1511.5%2529.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
Alteryx

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

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
Alteryx

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

Labels