I was wondering if there was a way to sum up values or count values using a hierarchy.
I have a hierarchy structure in my source data with the following structure:
Level1 > Level2> Level3>
Each level will have a set of children associated with it. So :
Level1
> L1Child1
> L1Child2
Level2
>L2Child1
>L2Child2
Level3
>L3Child1
>L3Child2
The branches in the hierarchy tree expand even further where the children branches will have their own branches.
So
Level1
>L1Child1
>>L1SubChild1
I have transactional data with sales data where each record has a SaleID with a Salesperson. Each Salesperson belongs to a specific level in the hierarchy. When a Salesperson moves up or down the hierarchy, his sales move with him.
So my sales data looks like:
SaleID
Sales Date
SalespersonID
0001
12/05/2015
1
0002
12/05/2015
2
The hierarchy lookup table looks something like:
Hierarchy Level
Parent
Child
Level1
NULL
L1Child1
Level1
NULL
L1Child2
Level2
Level1
L2Child1
Level2
Level1
L2Child2
L1Child1
Level1
L1Subchild1
The mapping of the salesperson to their respective level looks like this:
SalespersonID
Level
1
Level1
2
Level2
Based on the above structures and tables, a direct link between a Salesperson can be done. So when we want to find a count of the number of sales based on the hierarchy level this counts the number of sales for only that level. When Level1 is selected only 1 sale will be counted.
I want to be able to count the number of sales for the direct hierarchy level along with the children underneath.
So for example:
The number of sales for Level 1 should include all sales from Level1 up until the subchild of Level3 when selected in the dashboard. When a user selects Level2 all of the sales for the children and subchildren need to be counted EXCLUDING Level1.
Example:
The above sales tables shows 2 sales for salesperson 1 and 2. When I select Level1 this should calculate 2 sales and and 1 sale.
When I select Level2, this should show only 1 sale.
Can this be done? Are there any good solutions for this?
