topic Re: how to get the expand status when the pivot table is expand in QlikView

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

Anonymous — Fri, 09 Oct 2015 14:30:25 GMT

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

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

pamaxeed — Fri, 09 Oct 2015 14:36:01 GMT

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

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

jonathandienst — Fri, 09 Oct 2015 14:36:45 GMT

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.

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

Peter_Cammaert — Fri, 09 Oct 2015 14:45:12 GMT

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

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

Anonymous — Fri, 09 Oct 2015 15:53:57 GMT

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

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

Peter_Cammaert — Fri, 09 Oct 2015 16:16:10 GMT

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