Agrr Calculation

[jorditorras]

Dear Gurus, 

I have an scenario where one record (Node_ID) can have different Levels of Hierarchy. Then we have a KPI of Number of Hours for each one. In the example below, I want to calculate the number of Hours only in Level 3. For that I use an Aggr as 

Sum(Aggr(Sum([Hours]),[Level 3])

But it's summing the TOTAL of Hours (as if I was calculating the Total for Level 2: 

The result that I expect is 45.837 and not 190.207...

Any idea how to calculate this??

Thanks!!

[vinieme12 · Accepted solution]

It is returning the correct output given your data

there are two possible values in level 3

1) Area1

2)'-'   

If you only want to calculate level3 where field is not '-' or null then change expression to below,

sum({<Nodes_Id={"=len(Level3)>1"}>}Hours)  <<-- returns sum hours at level3 where level3 is not empty

Also since level3 is your last level, you don't need sum(Aggr(

Any other Level which is not the last level you can be calculated using the TOTAL keyword as below

For level2

sum({<Nodes_Id={"=len(Level3)>1"}>} TOTAL <Level2> Hours) <<-- returns sum hours at level2 where level3 is not empty

for Level1

sum({<Nodes_Id={"=len(Level3)>1"}>} TOTAL <Level1> Hours) 

<<-- returns sum hours at level1 where level3 is not empty

if you need to check if the corresponding level is not empty then change the set analysis  as below

sum({<Nodes_Id={"=len(Level2)>1"}>} TOTAL <Level2> Hours)   <<-- returns sum hours where level2 is not empty