Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Quamie
Contributor II
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
Contributor II
Contributor II
Author

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!

 

 

View solution in original post

Quamie
Contributor II
Contributor II
Author

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))

 

View solution in original post

3 Replies
Quamie
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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))

 

VivenReddy
Partner - Creator
Partner - Creator

Are you able to provide a sample qvw of your solution.

I'm finding that making selections at higher levels done select the correct levels below.