Subtotals on crosstabs

gharding

I have a crosstab that comes out nice.

I have three groupings a week number a name and then a date. Values are grouped by a field which works out great.

However, I need to be able to provide a subtotal after each name changes, as well as have a total on the right.

Is there a way to do that?

Thank you!

Updated note to include sample answer with the things I want in yellow highlight

Summer Intern CrossTab.xlsx

Transformation

Accepted answers

AGilbert

Here is a basic example of a subtotal by name and joined to each record of the original data. If you provide a sample workflow, and a mockup of the desired output, we can get you a better solution.

Screenshot 2024-05-13 142814.png

05132024_gharding.yxmd

All comments

AGilbert

Here is a basic example of a subtotal by name and joined to each record of the original data. If you provide a sample workflow, and a mockup of the desired output, we can get you a better solution.

Screenshot 2024-05-13 142814.png

05132024_gharding.yxmd

gharding

Thanks! However, i edited the post and left an attachment - the yellow highlight is what I am looking for.

Thanks

binu_acs

@gharding What are the expected output results based on your input file?

AGilbert

Try the attached to match your yellow markups.

edited: updated attachment to include the grand total row and make subtotal row null values = 0.

05132024_gharding_updated.yxmd

gharding

Agilbert - that was great. But still not enough.

The file was created as a crosstab, summarizing on a number and the columns may be less or more, depending on the file and the time of the month where more categories hit the report. I am trying to send a non-proprietary data file that will give you an idea. Will come shortly.

gharding

I have attached the workflow I have, and the crosstab that I had set up which gave me the original before the yellow lines I wanted. Hopefully you understand better now!

Thanks,

Gary

Start of data.xlsx

New Workflow2.yxmd

AGilbert

I connected my solution to your source data + cross tab tool. The output seems to match your expected format. The workflow should handle a variable number of rows and columns, but we can test it. Take a look at the attached output and let me know what isn't right.

05142024_gharding_crosstab.yxmd

output.xlsx

gharding

Thanks for everything @AGilbert as expected I forgot something. Can you adjust this - if I added another field to the pull? I added OFFC (col . I would try to replicate, but I am nowhere near that yet.

I appreciate the help. After this - it will be accepted as solution

Gary

Start of data.xlsx

AGilbert

Happy to help. I updated each Transform (orange) tool to include the new field as either a group by or key field. Essentially, treat it the same as other row headers like Week, Name, and Date.

Workflow and output attached. Let me know if something looks off or you need more explanation on how to keep it updated.

output.xlsx

05142024_gharding_crosstab2.yxmd

gharding

I fixed one thing to make the totals be correct for the day and people. However, I need to get a total for the week (will be 1 - 10).

@AGilbert you are a STAR! Can you help me get it to the finish line? Have learned a lot from this already, but don't want to screw it up. Cheers,

Gary

05142024_gharding_crosstab2.yxmd

AGilbert

Here is a solution. The previous workflow is inside a macro which works on each week's data at a time. Each week is returned with subtotals to the main workflow. Then the grand total is calculated before the final report is unioned together. I tried to add some notes to help explain.