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

[amien]

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

[amien]

see attachment. headcount doesn't roll-up

[amien]

/UP

[jonathandienst]

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

[amien]

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]

/up

[amien]

nobody?

[hdonald]

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]

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]

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.