Sorting by 2nd Dimension in a bar graph

[rjyothi]

I have created a bar chart. I have two dimensions. First is the month and second is the year. In the visual the years are displayed as 2019 2020 2018. The expected should be 2018 2019 2020. Drag and drop in sort does not work. I am not sure what to insert in the sort by expression box.

Appreciate any help on this.

[kaushiknsolanki]

Hi,

Go to Properties -> Sorting -> Select Year -> Switch off Auto -> Select "Sort Numerically"

[rjyothi]

I have tried all this earlier and nothing worked.

[lorenzoconforti]

I understand you said you've already tried changing the sequence of the sorting but, still, it looks like your second dimension is below your measure in the sorting order

[rjyothi]

Please view the attachment.

[lorenzoconforti]

if you swap the two dimensions around (i.e. to have month in the legend) do years sort correctly?

[rjyothi]

Yes if I swap them to have month in the legend they sort correctly. But the users want to see the year in the legend.

The months in the legend will not sort. Added the attachment.

[lorenzoconforti · Accepted solution]

I think they are actually sorting. The problem might be that the first year you have (2018) only starts from August

If you keep months in the legend and select 2019, do months sort correctly?

[rjyothi]

Yes they do. In the beginning I thought the same too but then I am new to Qlik  so was not sure. I will get additional data loaded and check if it behaves differently. Thank you for your time.

[lorenzoconforti]

It will depend on the first month of the first year; if it's not Jan, it will probably behave the same way

Another option is to force the chart to consider all months even when some are missing; you do this by using value list

Your month dimension will look like this:

=ValueList('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')

Your measure like this:

pick(match(ValueList('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')
,sum({<MONTH={"Jan"}>}VALUE)
,sum({<MONTH={"Feb"}>}VALUE)
,sum({<MONTH={"Mar"}>}VALUE)
,sum({<MONTH={"Apr"}>}VALUE)
,sum({<MONTH={"May"}>}VALUE)
,sum({<MONTH={"Jun"}>}VALUE)
,sum({<MONTH={"Jul"}>}VALUE)
,sum({<MONTH={"Aug"}>}VALUE)
,sum({<MONTH={"Sep"}>}VALUE)
,sum({<MONTH={"Oct"}>}VALUE)
,sum({<MONTH={"Nov"}>}VALUE)
,sum({<MONTH={"Dec"}>}VALUE)
)