topic Chart will not always sort by fiscal month in QlikView

Chart will not always sort by fiscal month

Mon, 03 Dec 2012 20:44:29 GMT

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

Re: Chart will not always sort by fiscal month

MayilVahanan — Mon, 03 Dec 2012 20:59:27 GMT

PFA

Re: Chart will not always sort by fiscal month

Tue, 04 Dec 2012 00:39:28 GMT

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.

Re: Chart will not always sort by fiscal month

Tue, 04 Dec 2012 17:43:49 GMT

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

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

Re: Chart will not always sort by fiscal month

Tue, 04 Dec 2012 17:48:52 GMT

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.

Re: Chart will not always sort by fiscal month

Tue, 04 Dec 2012 17:51:04 GMT

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.

Re: Chart will not always sort by fiscal month

Tue, 04 Dec 2012 17:54:48 GMT

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.

Re: Chart will not always sort by fiscal month

Tue, 04 Dec 2012 17:59:25 GMT

Got it. Thank you.