topic Pivot table - get field value when nodes are collapsed in QlikView

Pivot table - get field value when nodes are collapsed

Thu, 03 Feb 2011 16:12:20 GMT

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

Pivot table - get field value when nodes are collapsed

Thu, 03 Feb 2011 16:23:00 GMT

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)

Min(eac_method)

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

Pivot table - get field value when nodes are collapsed

Thu, 03 Feb 2011 17:16:33 GMT

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!

Pivot table - get field value when nodes are collapsed

Thu, 03 Feb 2011 22:36:01 GMT

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

Pivot table - get field value when nodes are collapsed

Sun, 06 Feb 2011 06:56:01 GMT

Thanks a lot, it works 🙂