topic Re: Fixed x-axis and sorting problem in QlikView

Fixed x-axis and sorting problem

Fri, 07 Sep 2012 21:56:53 GMT

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

Re: Fixed x-axis and sorting problem

Sat, 08 Sep 2012 03:58:24 GMT

Hello Eric,

Can you please post the sample solutions file.

Thanks & Regards,

Venkat T

Re: Fixed x-axis and sorting problem

whiteline — Sat, 08 Sep 2012 08:46:52 GMT

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;

Re: Fixed x-axis and sorting problem

Mon, 10 Sep 2012 16:38:44 GMT

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!