Summarize and Count By Two Columns

[KaraRademacher]

I attached what the process currently looks like in Excel and how I'm trying to tackle this in Alteryx using Summarize. I have a file with a list of participants that have been removed from a program.  For each participant I have their enrollment year and the year they were RFP (removed from program).  In excel we have rows for the enrollment year and columns for the RFP year.  Where these intersect we have a count.  Do I need transpose somewhere in here as well to combine this data?

[caitlynmcintyre]

Is your input for the workflow the excel file? Can you share an example of what the input looks like?

[caltang]

In addition to the sample file asked by @caitlynmcintyre - can you also provide your expected output in Excel as well? If you already have a workflow, can you export that to us as well so that we may see what you've done thus far and better guide you on what you can try/do next @KaraRademacher ?

[KaraRademacher]

@caitlynmcintyre and @caltang 

I have attached the workflow I started, the data for the workflow (RFP Summary), and the output we currently have in Excel that we are trying to replicate in Alteryx (Forfeiture Waterfall).  Appreciate any help you can provide to create the workflow that allows me to create this.

[caltang]

Done!

[caltang]

You were on the right track @KaraRademacher - your missing piece was Cross-Tabing your data to match what you want in your waterfall dataset. 

In addition, I noticed you have a "Grand Total" row and several rows for each year's totals. I've also added it for you towards the end. Feel free to add some spacing in between with a Text Input tool to your Union Tool.

Look to the bottom left of your Union Tool to re-arrange the order of the data streams by their # number.

If the above solved your need, kindly like & mark as accepted solution so that you may help others find the solution more quickly + to close the thread as is. Thanks!

Best regards,
Calvin Tang
https://www.linkedin.com/in/calvintangkw/

[KaraRademacher]

@caltang 

This is on the right track.  I attached the output file (Book 4) and the desire outcome (Forfeiture Waterfall) .  The columns look good but I'm hoping to group the rows differently.  I want to group first by Enrollment Year, second by RFP Year, third by Registrant ID.  If totals are added in Alteryx ideally they are under each Enrollment Year they are summing.  Right now they below all of the individual the registrant lines.  The totals are not a must, I can group in the excel output to get these pretty easily.

[caltang · Accepted solution]

Pretty simple - just use the Sort tool:

If the above solved your need, kindly like & mark as accepted solution so that you may help others find the solution more quickly + to close the thread as is. Thanks!

Best regards,
Calvin Tang
https://www.linkedin.com/in/calvintangkw/

[KaraRademacher]

Perfect!  Thanks @caltang 

[caltang]

My pleasure!