How to format a Cross table using populated hierarchy

[bertrand_herard]

Hello,

I've a little format problem in a cross table, I've attached a sandbox example.

I'm trying to display a hierarchy in a cross table. This hierarchy is populated, that is to say there are a value on each node off the hierarchy (it's not the usual case where there are only values at the leaf level then Qlik Aggregate data on Superior levels).

I want to display the value of each node on a non-fully expanded cross table.

I thought to use an "easy" formula : only({<depth=$(=min (depth))}>} value), but the issue is that min(depth) give good results in a cross table column, but if you use it in set analysis, it give the results ignoring cross tables dimensions where non selections are made.

If someone have an idea, I'm listening .

here's another way to explain it :

in a cross table, non fully expanded, how can I get the number of displayed dimension (i.e expanded dimension). Please note that there are no selections on expanded dimensions.

Best regards

[rubenmarin]

Hi Bertrand, if you uses Min(value) You'll get the minimum node value for the last dimension expanded.

Your expression "only({<depth=$(=min (depth))}>} value)" will fail because set analisys is calculated before drawing the table, so it's not row by row and "$(=min (depth))" will return '1' in all rows, wich is the min value for depth in the whole table.

I don't know a way to recover the last dimension expanded, maybe you can look to a workaround using Dimensionality() and SecondaryDimensionality()

[bertrand_herard]

Hi,

thank's for the suggestion

I think the min(value) won't work. value because node's value are not sorted by value (in my example I've set it to 1,2 3,4 ... just to make it simple), in real application father's node can have a greater value than it's son or not.

Dimensionality() and SecondaryDimensionalty doesn't work .  If you expand a column The value is always the same for each row (disregardind a null value of a column)

any other idea ?

Best regards