Chart will not always sort by fiscal month

[#1]

Hi,

This is my first post to QlikCommunity.

My chart should show the number of hours for each month in a fiscal year.  And the chart should show the months in fiscal year month order.  October = 1, November = 2, through September = 12.  The part that doesn't work is the chart does not always show the months in the correct order.

The chart has one expression to reset the selection from a selected month to a fiscal year.

sum({$<
    calendar.fiscal_year = {"$(=max(calendar.fiscal_year))"},
    calendar.yearmonth=
    >}hc.annual_leave_hours) 

The sort tab in the chart uses a fiscal month number to sort.  October = 1, November = 2, etc.

calendar.fiscal_month_sort

If no selection is made, the months are shown in fiscal month order

If a month is selected, the months are not shown in fiscal month order.  And the order of the months varies depending on the month selected.

How can I force the months to always appear in fiscal month order?

The attached qvw shows what happens.  It has two sheets.  The sheets show the same issue, but they have different data models.  I simplified the data model in sheet - Model 2, to try to isolate the problem.

The excel is the data behind the QVW.

The examples in the QVW are just a starting point.  Additional expressions, data sets and selection criteria will be added once this issue is figured out;

Thank you,

Steve

[MayilVahanan]

Hi

PFA

[#3 · Accepted solution]

Use a set analysis.  Set your sort expression to: Only({1}calendar.fiscal_month_sort)

The {1} signifies that the current selection should be ignored, so the sort will be correct even when you select certain months.

[#4]

John and Mayil, thank you.  You both had the same answer.

I understand why {1} is used.  What is the purpose of using Only?

[#5]

The help text describes Only() better than I could:

Only([{set_expression}] [distinct] [total[<fld {, fld}>]] expression)

If expression or field iterated over the chart dimension(s) contain one single value, that value is returned, else NULL is returned.  Only can return numeric values as well as text values.

[#6]

I'd looked at that too, but couldn't figure out what that meant in the context of my issue.  Thanks, Steve

Edited:  I'll get there -- still figuring QlikView out.

[#7]

Ah, sorry, didn't mean for my answer to be evasive.

I used Only() because it seemed like the most fitting given your situation.  Each fiscal months would have only one possible value for fiscal_month_sort, Only() would return that value.

But you could just as easily have used Max() or Min() (or other functions) in this situation.

[#8]

Got it.  Thank you.