Pivot table - get field value when nodes are collapsed

[#1]

Hi,
I use a pivot table to display a hierarchy,
the dimensions of this pivot table are 3 fields from the hierarchy
I need to display as an expression a field , which has different values for each dimension fields
the problem is that when the nodes are collapsed I get null as a value, only when the nodes are fully expanded I get a real value.
I think I should use a set analysis for this expression , but I wasn't able to write anything that works...
attached a file with the example
Thanks for any help,
Nitza

[#2]

When it is collapsed you are doing an aggregation, since your field doesn't have an aggregation function, QlikView has a choice of 3 or 4, but no logic telling it which one to pick, so you get a null.

Which value should appear, 3 or 4? If 4, then use:

Max(eac_method)

Or

Min(eac_method)

Adding an aggregate function will allow QlikView to display a value when collapsed.

[#3]

the value that's I'm looking to get is 1 or 2

I need to get the value from DATA table where wbs_id=NodeNameX

where X=dimendionality()

Thanks!

[#4 · Accepted solution]

It's ugly, but it works:

Pick(Dimensionality(),
 Only(Total If(IsNull(NodeName3) and IsNull(NodeName2), eac_method)),
 Only(Total If(IsNull(NodeName3) and Not IsNull(NodeName2), eac_method)),
 Only(eac_method)
)

[#5]

Thanks a lot, it works 🙂