Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

How to keep custom sort order in bar chart when displaying all values?

I have a simple bar chart showing production by hour. As the shift starts at 6am I have added the following sort expression;

"Match([Hour], '6', '7', '8','9', '10', '11', '12', '13', '14', '15', '16','17', '18', '19', '20', '21', '22','23', '0', '1', '2', '3', '4', '5'  )  "

This works but I would like the bar chart to display hours where there is no production. I therefore click the 'show all values' button on the dimension tab. The result of this is to move all of the 0 value bars to the left of the chart as in the screen shot below.

Bar chart by hour.png

Does anyone know how it i possible to keep these 0 value bars in their sort order?

1 Solution

Accepted Solutions
MVP
MVP

Re: How to keep custom sort order in bar chart when displaying all values?

Hi

I think the there is no Hour value for the hours where no production takes place, so the match fails for those bars (and returns 0).

If this indeed the problem, one possible solution would be to create a dummy production value of 0 for these hours. Or create a separate table containing all 24 hours with the same field name, Hours. A bonus of this is that you can use the load order to sort - provided you create this table before loading the production data. A simple inline load will do it:

Hours:

LOAD * Inline

[

    Hour

     6

     7

     8

...

     4

     5

];

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
6 Replies
thornofcrowns
Valued Contributor II

Re: How to keep custom sort order in bar chart when displaying all values?

Sort by Load Order?

Not applicable

Re: How to keep custom sort order in bar chart when displaying all values?

Hi James

That does't work. It gives the random order below;

Bar chart by hour2.png

Re: How to keep custom sort order in bar chart when displaying all values?

In sort >> expression >> type your expression like =Sum(BATCH_SIZE) and un check all option in sort.

And select Descending order

Re: How to keep custom sort order in bar chart when displaying all values?

Like this ?

MVP
MVP

Re: How to keep custom sort order in bar chart when displaying all values?

Hi

I think the there is no Hour value for the hours where no production takes place, so the match fails for those bars (and returns 0).

If this indeed the problem, one possible solution would be to create a dummy production value of 0 for these hours. Or create a separate table containing all 24 hours with the same field name, Hours. A bonus of this is that you can use the load order to sort - provided you create this table before loading the production data. A simple inline load will do it:

Hours:

LOAD * Inline

[

    Hour

     6

     7

     8

...

     4

     5

];

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: How to keep custom sort order in bar chart when displaying all values?

Hi Jonathan

Loading an inline table and sorting from that worked great.

Thanks

James

Community Browser