Hi everyone,
I have a table with the sales per Sales Rep and my challenge is to calculate the sales generated by each sales team, therefore aggregating the sales per team.
I have a table like that with the sales per Sales Rep (RSM):
| RSM | Year | … | Sales |
| 1 | 2021 | | 5.000 |
| 2 | 2021 | | 4.000 |
| 3 | 2021 | | 3.200 |
| 4 | 2021 | | 2.560 |
| 5 | 2021 | | 2.048 |
| 6 | 2021 | | 10.240 |
| 7 | 2021 | | 51.200 |
| 8 | 2021 | | 256.000 |
| 9 | 2021 | | 204.800 |
| 10 | 2021 | | 163.840 |
| 1 | 2020 | | 131.072 |
| 2 | 2020 | | 2.048 |
| 3 | 2020 | | 10.240 |
| 4 | 2020 | | 51.200 |
| 5 | 2020 | | 256.000 |
| 6 | 2020 | | 204.800 |
| 7 | 2020 | | 163.840 |
| 8 | 2020 | | 131.072 |
| 9 | 2020 | | 204.850 |
| 10 | 2020 | | 163.890 |
Then, I have a separate table with the head of each Sales Rep (VORG1), which form a sales team. In some cases, the VORG1 has another sales head, indicated in VORG2. The table looks like that:
| RSM | VORG1 | VORG2 |
| 1 | 1 | 1 |
| 2 | 1 | 1 |
| 3 | 1 | 1 |
| 4 | 4 | 1 |
| 5 | 4 | 1 |
| 6 | 4 | 1 |
| 7 | 4 | 1 |
| 8 | 9 | 9 |
| 9 | 9 | 9 |
| 10 | 9 | 9 |
My goal is to have a table with the sales per Sales Rep (RSM) and another column with the accumulated sales per team. Like that:
| RSM | Year | … | Sales | VORG1 | VORG2 | Sales_VORG1 | Sales_VORG2 |
| 1 | 2021 | | 5.000 | 1 | 1 | 9.760 | 329.248 |
| 2 | 2021 | | 4.000 | 1 | 1 | 9.760 | 329.248 |
| 3 | 2021 | | 3.200 | 1 | 1 | 9.760 | 329.248 |
| 4 | 2021 | | 2.560 | 4 | 1 | 319.488 | 329.248 |
| 5 | 2021 | | 2.048 | 4 | 1 | 319.488 | 329.248 |
| 6 | 2021 | | 10.240 | 4 | 1 | 319.488 | 329.248 |
| 7 | 2021 | | 51.200 | 4 | 1 | 319.488 | 329.248 |
| 8 | 2021 | | 256.000 | 9 | 9 | 499.712 | 499.712 |
| 9 | 2021 | | 204.800 | 9 | 9 | 499.712 | 499.712 |
| 10 | 2021 | | 163.840 | 9 | 9 | 499.712 | 499.712 |
| 1 | 2020 | | 131.072 | 1 | 1 | 63.488 | 819.200 |
| 2 | 2020 | | 2.048 | 1 | 1 | 63.488 | 819.200 |
| 3 | 2020 | | 10.240 | 1 | 1 | 63.488 | 819.200 |
| 4 | 2020 | | 51.200 | 4 | 1 | 755.712 | 819.200 |
| 5 | 2020 | | 256.000 | 4 | 1 | 755.712 | 819.200 |
| 6 | 2020 | | 204.800 | 4 | 1 | 755.712 | 819.200 |
| 7 | 2020 | | 163.840 | 4 | 1 | 755.712 | 819.200 |
| 8 | 2020 | | 131.072 | 9 | 9 | 368.740 | 368.740 |
| 9 | 2020 | | 204.850 | 9 | 9 | 368.740 | 368.740 |
| 10 | 2020 | | 163.890 | 9 | 9 | 368.740 | 368.740 |
There's a similar question posted here: https://community.qlik.com/t5/App-Development/Accumulative-hierarchy-calculation-in-script/td-p/1659...
Unfortunately, I didn't manage to get the desired output. The main difference is that I need the sales of the parents and not the children.
Any help is highly appreciated!
