Option to Collapse Inner Dimensions in Table and Straight Table Limitation

[Benno]

We are building a table with a "fixed number" Limitation in combination with a variable in Qlik Cloud. We have also selected "show others" in the Limitation. In addition we added a slider to be able to change the number of our top products via the variable.
When using more than one dimension we have the problem that the other values of the first dimension are expanded to more than one row with values for the other dimension (see left image in the screenshot). To solve this, QlikView had the option to "Collapse inner Dimensions" (see screenshot lower right in german).
We need an option like this in Qlik Cloud to be able to build the table in a simmilar way.

Does anybody know of a solution or a workaround?

I have also posted this issue as an ideation, in case anybody else sees this as an issue please upvote.

https://ideation.qlik.com/app/#/case/371435?currentProductId=9da99bc1-0dfb-4471-8e12-d23685578c43 

Thanks for your help

Qlik Cloud 

 

[stevedark · Accepted solution]

Hi @Benno 

Good call putting this into Ideation, it should be there as it was a feature that was used in QlikView.

I would imagine the only way of doing this at the moment would be to have two calculated dimensions that use the rank function.

The first dimension is relatively straight forward, thus:

=aggr(if(rank(sum(Value)) <= vTop, Dim, 'Other'), Dim)

The second is a bit more complicated, as you need to work out the rank of the first dimension, but return the value of the second dimension, within the first. So the expression reads:

=aggr(if(aggr(if(rank(sum(Value)) <= vTop, Dim, 'Other'), Dim) = 'Other', 'Other', Dim2), Dim, Dim2)

I have mocked this up in an app using the following load script:

let vTop = 4;

Data:
LOAD
'' & num(ROwNo(), '0000') as Dim,
'' & num(floor(rand()*3), '0000') as Dim2,
floor(rand()*1000) as Value
AUTOGENERATE(20);

You can play about with the numbers to get other test data in.

Rank can sometimes be a bit funny when you get equal values, I think you probably want to look into the parameters of the rank function to set that to your requirements for when there are exact matches between two dimensions.

Hopefully that is helpful and you can use it with your own data.

Cheers,
Steve

[Benno]

Hi @stevedark 

Thanks a lot! This totally solves my problem.

Cheers,
Benno