Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Quamie
New Contributor II

Hierachy Roll-Up totals in tree view

Hi All,

I'm having trouble finding a solution to providing roll-up totals in my Hierarchy tree view.

hierarchy1.PNG

I know that I can use HierarchyBelongsTo to do this, but that does not provide a tree-view option like Hierachy does, basically, I want to combine these together.

Any thoughts?

hierarchy2.PNG

Can you create a new table that loads AncestorName and calculates Count(Name) in the load script?

Can I use an expression or Join that calculates Count(Name) for AncestorName and links that value to the Tree-View of my PathTree?

Maybe there is something easy I'm missing? 😞

 

Script;

 

Directory;
Hierarchy(Child_ID, Parent_ID, Child_Name,Department,Child_ID,PathName,"/",Depth)
LOAD Parent_ID, 
     Child_ID,
     Child_ID as Child_Name
FROM
FinanceMappingTest.xlsx
(ooxml, embedded labels, table is [New Load]);

BelongsTo:
HierarchyBelongsTo(Child_ID, Parent_ID, Child_NameB, AncestorID, AncenstorName, DepthDiff)
LOAD
Parent_ID,
Child_ID,
Child_ID as Child_NameB
FROM
FinanceMappingTest.xlsx
(ooxml, embedded labels, table is [New Load]);


Directory;
LOAD Name, 
     Child_ID,
     Job,
     Manager,
     Location
FROM
FinanceMappingTest.xlsx
(ooxml, embedded labels, table is [Person Load]);

 

Thank you for any assistance! 🙂 

2 Solutions

Accepted Solutions
Quamie
New Contributor II

Re: Hierachy Roll-Up totals in tree view

Hi All,

I've gotten this partially working by following some information posted here;

https://community.qlik.com/t5/QlikView-App-Development/hierarchy-question-using-expression-to-sum-up...

My hierarchy now correctly calculates roll-up totals used in my HierarchyBelongsTo table using this expression;

SUM(if(Child_ID=AncestorID,aggr(count(Name),AncestorID),0))

The only caveat is, when a field is selected, all other fields go to zero. Can I add something to the expression for it to ignore selections?

 

Thank you!

 

 

Quamie
New Contributor II

Re: Hierachy Roll-Up totals in tree view

Hi All,

Found the solution, just had to add Set Expression to the calculation. Sorry for wasting your time!

SUM({1}if(Child_ID=AncestorID,aggr(count({1}Name),AncestorID),0))

 

2 Replies
Quamie
New Contributor II

Re: Hierachy Roll-Up totals in tree view

Hi All,

I've gotten this partially working by following some information posted here;

https://community.qlik.com/t5/QlikView-App-Development/hierarchy-question-using-expression-to-sum-up...

My hierarchy now correctly calculates roll-up totals used in my HierarchyBelongsTo table using this expression;

SUM(if(Child_ID=AncestorID,aggr(count(Name),AncestorID),0))

The only caveat is, when a field is selected, all other fields go to zero. Can I add something to the expression for it to ignore selections?

 

Thank you!

 

 

Quamie
New Contributor II

Re: Hierachy Roll-Up totals in tree view

Hi All,

Found the solution, just had to add Set Expression to the calculation. Sorry for wasting your time!

SUM({1}if(Child_ID=AncestorID,aggr(count({1}Name),AncestorID),0))