topic Re: Matrix Show all Values(x,y axes) but hide null in QlikView

Matrix Show all Values(x,y axes) but hide null

Anonymous — Wed, 02 Mar 2016 10:14:32 GMT

Dear Community,

i need to build a matrix with A and B axes.

All the time all possible values of A and B should be visible, so the matrix should be fully expanded.

For such a model i use a pivot with two dimensions A&B and an expression with simple 'x'.

The dimensions A and B have simple inline data 1,2,3,4,5 and i checked "show all values" for them.

Also there is data for A and B combinations but not for all possible combinations.

=> The Problem is that also null values of A & B are displayed

If i check "Suppress When Value is Null" for the dimensions, i lose the benefit of "show all values" as for these dimensions

combination where we dont have data, the dimension value is not displayed.

so how can i make it pssible to have all possible dimensions without NULL inside the matrix

with data that is not for all combinations of A&B present.

Best regards!

Re: Matrix Show all Values(x,y axes) but hide null

sunny_talwar — Wed, 02 Mar 2016 10:16:57 GMT

Would you be able to share the sample you are working with above?

Re: Matrix Show all Values(x,y axes) but hide null

Anonymous — Wed, 02 Mar 2016 11:23:25 GMT

Hi Sunny, i have added a sample script.

I appreciate your help

Re: Matrix Show all Values(x,y axes) but hide null

Chanty4u — Wed, 02 Mar 2016 11:25:27 GMT

is this

Re: Matrix Show all Values(x,y axes) but hide null

Anonymous — Wed, 02 Mar 2016 11:30:23 GMT

Hi Chanty, this is not correct as the Dimension A loses the value 5!

Re: Matrix Show all Values(x,y axes) but hide null

sunny_talwar — Wed, 02 Mar 2016 11:30:25 GMT

Try the attached

Re: Matrix Show all Values(x,y axes) but hide null

Anonymous — Wed, 02 Mar 2016 11:49:53 GMT

Hi Sunny,

'x' was just an example try now the values X

So using your expression =If(Len(Trim(B)) > 0, X) you will see it's not working as the matrix does not show all dimensions as there are not for all combination values available.

Re: Matrix Show all Values(x,y axes) but hide null

sunny_talwar — Wed, 02 Mar 2016 11:56:33 GMT

Try this expression in that case:

=If(Len(Trim(B)) > 0, If(Len(Trim(X)) > 0, X, ' '))

Re: Matrix Show all Values(x,y axes) but hide null

Anonymous — Wed, 02 Mar 2016 12:40:22 GMT

Thanks Sunny,

this goes into the right direction but still nneds some improvement:

There are some values missing inside the matrix for example there are multiple values for A=2 & B=2

So we use a count(X) but then we still need to hide the columns where the dimension is null

Check the file & thanks for your support!

Re: Matrix Show all Values(x,y axes) but hide null

sunny_talwar — Wed, 02 Mar 2016 12:43:39 GMT

Try this out:

=If(Len(Trim(B)) > 0, If(COUNT(X) = 0, '-', Count(X)))

Re: Matrix Show all Values(x,y axes) but hide null

Anonymous — Wed, 02 Mar 2016 12:47:47 GMT

Wow Sunny so fast and good, that was it! Thanks so much!

Re: Matrix Show all Values(x,y axes) but hide null

sunny_talwar — Wed, 02 Mar 2016 13:09:25 GMT

Awesome, I am glad we were quickly able to figure it out