Grouping and summarizing data with a specific desired output

Question

Hello,

I have a data set that I need to group and total a specific way, and I get stuck after summarizing it. Screenshots of Original data, summarized data, and desired output below. Plus a workflow attached with this data. The actual data will of course have a lot more records with different Teams/Sub Teams etc.

Original data set

Summarized data - note, new column "Active" is a count of the "Record Number"

Desired output

Thank you in advance!

Dummy data test.yxmd

AncientPandaman · Answer

@kas for active column. i using summarize tool (group by first 3 column and get the count) then join it back.

because i not understand the logic, you need to adjust the group by in both summarize and join tool.

kas · Answer

@PangHC

Thank you!...this got me very close...only thing needed is to total the Active column after everything has been grouped (in this example "Initial" total under the Active column should be 5, and "Interims" total would be 7)...and I can't see a way to do that at this point.  Any more ideas would be great!

Thank you,

Kim

AncientPandaman · Answer

@kas i found a link. 
How to Create Merged Cells in Reports, Part 1 - Alteryx Community

example in last browse tool, workflow, and excel output

Spoiler

where you can refer but i not suggest.

because i suggest you do it in excel.
where you can create a template.
output to this excel template (via blob input and output) link:Alteryx: Excel Templates with Blob Tools – Intersections and Overlaps

then just refresh the pivot.