topic Re: Sort and display dimension when value is null in QlikView

Sort and display dimension when value is null

Anonymous — Wed, 21 Oct 2015 14:12:51 GMT

Greetings Qlikview Experts,

I have created a bar chart based on the dimension Enrollment Status. There are only 3 values to this dimension. Employee, Spouse and Children. I would like this to be the sort order so I created the formula below in the sort expression box.

Everything works great until the spouse dimension value is null. I can choose show all values in the dimension properties and it shows up at the end of my chart. I have a corresponding straight table that I use as well and I get the same incorrect result. Since children has a covered live, value it is not null. I use covered lives as a first expression in the bar chart, but I am not displaying it.

I have tried using a calculated dimension and I still can't get it to display properly

How can I ALWAYS display all of my dimensions values in the order I want, regardless of nulls.

Re: Sort and display dimension when value is null

sunny_talwar — Wed, 21 Oct 2015 14:25:50 GMT

Try this:

=Match(Only({1} [Enrollment Status], 'Employee', 'Spouse', 'Children')

=Match(Only({1} [Enrollment Status]), 'Employee', 'Spouse', 'Children')

UPDATE: Missed a parenthesis

Re: Sort and display dimension when value is null

Anonymous — Wed, 21 Oct 2015 14:46:10 GMT

That formula in a calculated dimension field, displays the error message // error in calculated dimension and only shows me the Employee value bar

Re: Sort and display dimension when value is null

sunny_talwar — Wed, 21 Oct 2015 14:49:14 GMT

Use this on the sort tab of the chart properties

Re: Sort and display dimension when value is null

Anonymous — Wed, 21 Oct 2015 14:56:07 GMT

Yes. That solves my sort order issue as long as I have show all values checked in the dimension properties. Thanks for your help

Re: Sort and display dimension when value is null

sunny_talwar — Wed, 21 Oct 2015 14:57:02 GMT

Aweomse

I am glad it helped.

Best,

Sunny