Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!