Summarize and Count By Two Columns

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/

Hi @KaraRademacher

I've managed to fix up Table 2 with the nulls like your design here:

All comments

caitlynmcintyre

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

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 ?

@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.

Forfeiture Sample.yxmd

RFP Summary.xlsx

Forfeiture Waterfall.xlsx

Done!

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.

@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.

Book4.xlsx

Forfeiture Waterfall.xlsx

Pretty simple - just use the Sort tool:

Perfect! Thanks @caltang

My pleasure!

@caltang hoping you can help. Trying to do a couple more things with the same data. I have attached the workflow that contains the forfeiture waterfall you helped me create. I attached the excel file I currently maintain where I use this data. The excel has a few charts. The first is the waterfall you helped me create. (I would like to add the total enrollees column and the Aug rate column to the right). The total enrollee data is another input I have added in the workflow. The second chart assigns percentages to the data in the first chart. The third chart then takes the percentages and predicts the number of active participants that will forfeit based on the waterfall.

If the creation of these other charts is too complicated I can create the first in alteryx and do the remaining steps in Excel.

Forfeiture Sample.yxmd

Forfeiture Excel.xlsx

Sure I can help. I’m outside at the moment, do you mind waiting for a bit?

@caltang sure, no rush. Thanks!

Hi @KaraRademacher

Just putting this WIP here first, I solved your first 2 tables. Your 3rd table will require some time. But I just had a long day, and I just go back not too long ago.

Sorry but I'll have to put this on hold until Friday when I have a bit more time on my hands. For now, this should work in your scenario.

Sure thing @caltang

Hi @KaraRademacher

I've managed to fix up Table 2 with the nulls like your design here: