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?

[KaraRademacher]

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

[caltang]

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

[KaraRademacher]

@caltang sure, no rush.  Thanks!

[caltang]

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.

[KaraRademacher]

This looks good!  Thanks!

[caltang]

When Friday comes, do you mind tagging me in this post again so that I won’t forget? Thanks @KaraRademacher 

[KaraRademacher]

Sure thing @caltang 

[caltang · Accepted solution]

Hi @KaraRademacher 

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

[caltang]

Though I would say that the third table is quite difficult to do:

Because it takes into account the year which it starts, but will keep rolling and repeat the sum of %s in Table 2 from the last values, you'll need something like an iterative macro to resolve this.

I'm stumped on this at the moment - I recommend posting this as a new question on the Community so that we can see what others can do as well. Hope this helps somewhat.

[KaraRademacher]

Thank you @caltang!  I already have the formulas in excel for the third chart so I think it's just as easy to drop this at the bottom of the alteryx output in excel and let it do this calculation.  I appreciate you helping me get to this point.