Fixed x-axis and sorting problem

[#1]

Hi all. So I have a chart where the X-axis dimension is text, not numeric values. On the x-axis, I have low, medium, and high, and on the y-axis I have a count(distinct ID) expression.

I want the chart to display low, medium, and high, regardless of my selections. By default, if I select a category with only low records, the chart will get rid of the medium and high bars completely. I want it to show low, medium, and high, even if the values are zero.

I went to the Presentation tab and unchecked "Suppress Zero-Values" and this got me halfway there. Now, all bars show up even if they're zero, but now the sorting is screwed up.

So I wanted the bars to always show up as low, then medium, then high, in that order, so on the Sort tab I set it to sort by this expression:

=if([LMH] = 'low', 1, if([LMH] = 'medium', 2, if([LMH] = 'high', 3)))

This works under the default configuration, but when I suppress zero-values, the sorting gets screwed up. It shoves all the bars with zero values to the right, leaving the bars with values all the way to the left.

Any ideas? Let me know if you have questions. Thanks!

Eric

[#2]

Hello Eric,

Can you please post the sample solutions file.

Thanks & Regards,

Venkat T

[whiteline · Accepted solution]

The easiest way is to load your string values [LMH] in the right order when the script starts. And then you can use Load Sort order.

For example:

LMHSortOrder:

LOAD * INLINE [

LMH

low

medium

high

];

In the end you can drop it (QV remembers first loading order)

drop table LMHSortOrder;

[#4]

Using the load order seemed to work. I set the chart to show all values, and unchecked suppress zero values, then I set the sort to Load Order, and it seems to be working fine now. Thanks a lot!