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
Solved! Go to 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
Thanks a lot Ben!
This solved my question.
Thanks,
Daniel