Sort and display dimension when value is null

[Anonymous]

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.

[sunny_talwar]

Try this:

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

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

UPDATE: Missed a parenthesis

[Anonymous]

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

[sunny_talwar · Accepted solution]

Use this on the sort tab of the chart properties

[Anonymous]

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

[sunny_talwar]

Aweomse

I am glad it helped.

Best,

Sunny