Aggregating data based on hierarchical levels

Tue, 12 May 2015 06:46:50 GMT

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.

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?

Re: Aggregating data based on hierarchical levels

Gysbert_Wassenaar — Tue, 12 May 2015 07:54:27 GMT

Perhaps this discussion helps: Re: BOM Explosion using Hierarchy & HierarchyBelongsTo

topic Re: Aggregating data based on hierarchical levels in QlikView

Aggregating data based on hierarchical levels

Re: Aggregating data based on hierarchical levels