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: 
amien
Specialist
Specialist

hierarchy question, using expression to sum up all the level - rollup

i have a material hierarchy which is all loaded into one table. Its from bottom to top.

Pathname is like this .../...../.....

All normal..

But now i want to load another table which links to the hierarchy table. This new table contains to columns. MaterialID (for linking to the hierarchy table) and a column which contains # of days to produce.

now i pu this in my expression : SUM(Days)

this all works fine, but when i 'roll-up' the total days are not calculated. When i roll-up to the highest level. I will see the number of days of the highest level. It doesn't to a sum of all the days that are also on a lower level.

how can i fixed this

11 Replies
amien
Specialist
Specialist
Author

see attachment. headcount doesn't roll-up

amien
Specialist
Specialist
Author

/UP

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

I can't load your file here due to firewall restrictions. It sounds like you are linking days at the top level of the hierarchy. For the hierarchy to work properly you need to link at the bottom level of the hierarchy.

I assume that the hierarchy contains a field for each level, not just a treeview field.

If that's not the problem I suggest you post a sample qvw with the problem.

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
amien
Specialist
Specialist
Author

updated my example.

Now i do a count on employeeID which is the lowest level

i'm not sure what my expression would be like. COUNT(ID) again a Path dimension will always be 1 hit.

amien
Specialist
Specialist
Author

/up

amien
Specialist
Specialist
Author

nobody?

hdonald
Creator
Creator

Hi,

I've tried that kind of roll up on the example 'Whats New in v11' document.

It uses 'hierarchy belongs to' to create a table you can use to group results by with an aggr expression - e.g. sum(if([Employee Name]=Superior ,aggr(sum(EmployeeCounter),Superior),0))

- see updated version of your example attached.

Maybe others have a more concise solution.

It would probably be better to script a summary table by employee if your hierarchy is a large one,

Regards,

HD.

amien
Specialist
Specialist
Author

many thanks for your reply hdonald.

your example is great .. but i have a question.

the values only show when you select something. How can i make it, on all levels, visible, even when nothing is selected?

=sum({<PathName=>} if(MATERIAL_NAME=Superior,aggr(sum({<PathName=>} PurchTerm),Superior),0))

this allmost works, but i dont have values for all level (i have 8 in total)

hdonald
Creator
Creator

Hi,

I did have a problem with 0 values appearing for the same employee name appearing in two different teams - e.g. in the 'Whats New...' data example the name 'Edward Smith' appears twice with different Superiors.

I changed the expression to use the unique Employee Number instead of Name ....something like ...

sum(if([Employee Number]=[Superior Number] ,aggr(sum(EmployeeCounter),[Superior Number]),0))

and that did the trick.

Perhaps you have a similar issue ? - maybe try with Material Number / Superior Number ?

Regards,

HD.