how to get the expand status when the pivot table is expand

[Anonymous]

Hi, all,

Got a problem to catch the status when ANY ROW is expand in a pivot table.  The table is originally collapsed, and when the table is in expand status, we would like to hide some expression columns that only make sense when  the table is in collapse status.

I know how to use the macro and variable to expand all/ collapse all.  But have no idea how to catch the status when the pivot table is expand.

Any one can help?

Thanks very much

[pamaxeed]

HI,

I think there is not such event, that u can catch.

One of our customer had the same requirement but I think it is not possible, to get the current position in the tree in a pivot table for each row....

Try to give a look on this post:

https://community.qlik.com/thread/68363

Patric

[jonathandienst]

There is no event raised when the table is expanded or collapsed, so you cannot fire an action or macro in response to the user expanding/collapsing the table.

If you can use macros (no Ajax), then you could create buttons to collapse/expand the table and hide or show columns at the same time.

[Peter_Cammaert]

Use the dimensionality() function in your expressions to detect how exactly your pivot table has been collapsed/expanded.

• dimensionality() = 0 means Totals row
• dimensionality() = 1 means level 1 (first dimension value line or one dimension level without aggregation)
• and so on.

See the QV Desktop help article on dimensionality(). It has a very clear explanation and a link to some very good examples.

Note that this works only for horizontal dimensions. For vertical dimensions, you have to use secondarydimensionality()

Best,

Peter

[Anonymous]

Hi, Peter,

This is really a nice function. Thought I can not use it as  expression condition to hide the column (it dose not work), but I can define new expression value by using value of dimensionality() as conditions , like 'N/A'  to the expression columns to identify the data can not be used.

Thanks very much

[Peter_Cammaert]

No indeed, you can't use it in a conditional show for an expression as the function wouldn't know what to return.

However, you can use it in the expressions themselves. For example,

=IF (dimensionality() <= 1; Sum(stuff))

will force an arbitrary expression to return all NULL values in every cell when a pivot table is expanded. That's already one step in the right direction.

The next one is a bit tricky. Pivot tables won't make expression columns disappear, even if they contain nothing more than NULL values. There is a technique however (using an INLINE Table or ValueList()) to create dynamic expressions instead of fixed-column expressions. And those will disappear instantaneously as soon as every cell contains a NULL-value.

See Re: Hide a column in Pivot Table for more information and examples.

Best,

Peter