How to tell Alteryx to group by fields, sum the totals, then output into several files?

[danielstefanng]

Hi,

I have the following data set below. Please note that the dates are in dd-mm-yyyy format.

Name	Date	Total Fee Excl GST	Total Fee Incl GST
Person 1	23/07/2018	740	814
Person 1	3/09/2018	740	814
Person 1	6/09/2018	740	814
Person 1	22/10/2018	1460	1606
Person 1	23/10/2018	950	1045
Person 1	24/10/2018	950	1045
Person 1	25/10/2018	1040	1144
Person 2	13/08/2018	415	456.5
Person 2	13/08/2018	325	357.5
Person 2	14/08/2018	415	456.5
Person 2	14/08/2018	325	357.5
Person 2	15/08/2018	415	456.5
Person 2	15/08/2018	325	357.5
Person 2	16/08/2018	415	456.5
Person 2	16/08/2018	325	357.5
Person 3	3/09/2018	595	654.5
Person 3	3/09/2018	745	819.5
Person 3	4/09/2018	415	456.5
Person 3	4/09/2018	535	588.5
Person 3	5/09/2018	415	456.5
Person 3	5/09/2018	535	588.5
Person 3	6/09/2018	505	555.5
Person 3	6/09/2018	325	357.5

What I would want to happen is to group these by Person, then by Week, then sum up the Fee values by adding an extra row as Total, and then finally, output them into separate Excel files. Is this possible?

The below what I expect to have in separate Excel spreadsheets.

Output 1: 23/07/2018 is the only date in that week

Name	Date	Total Fee Excl GST	Total Fee Incl GST
Person 1	23/07/2018	740	814
Total		740	814

Output 2: 3/09/2018 and 6/09/2018 are part of the same week

Name	Date	Total Fee Excl GST	Total Fee Incl GST
Person 1	3/09/2018	740	814
Person 1	6/09/2018	740	814
Total		1480	1628

Output 3: 22/10/2018 - 25/10/2018 are part of the same week

Name	Date	Total Fee Excl GST	Total Fee Incl GST
Person 1	22/10/2018	1460	1606
Person 1	23/10/2018	950	1045
Person 1	24/10/2018	950	1045
Person 1	25/10/2018	1040	1144
Total		4400	4840

Output 4: 13/08/2018 - 16/08/2018 are part of the same week

Name	Date	Total Fee Excl GST	Total Fee Incl GST
Person 2	13/08/2018	415	456.5
Person 2	13/08/2018	325	357.5
Person 2	14/08/2018	415	456.5
Person 2	14/08/2018	325	357.5
Person 2	15/08/2018	415	456.5
Person 2	15/08/2018	325	357.5
Person 2	16/08/2018	415	456.5
Person 2	16/08/2018	325	357.5
Total		2960	3256

Output 5: 3/09/2018 - 6/09/2018 are part of the same week

Name	Date	Total Fee Excl GST	Total Fee Incl GST
Person 3	3/09/2018	595	654.5
Person 3	3/09/2018	745	819.5
Person 3	4/09/2018	415	456.5
Person 3	4/09/2018	535	588.5
Person 3	5/09/2018	415	456.5
Person 3	5/09/2018	535	588.5
Person 3	6/09/2018	505	555.5
Person 3	6/09/2018	325	357.5
Total		4070	4477

Thanks in advance for your assistance!

Kind regards,

Daniel

[BenMoss · Accepted solution]

Hi @danielstefanng,

Here is a solution...

Things to look out for are how we use the summerize tool to aggregate our data.

How I've generated a dynamic path for the different xlsx files by concating the week and name together with a path in the formula tool, and then how we can use this to split our data into different files using the output tool.

You will have to ammend the file path part as at present it's pointing to a location on my machine.

Ben

[danielstefanng]

Thanks a lot Ben!

This solved my question.

Thanks,

Daniel