topic Re: How do I order months in a bar chart chronologically in QlikSense? in App Development

How do I order months in a bar chart chronologically in QlikSense?

Thu, 14 May 2015 00:35:46 GMT

Hello,

How can I order months chronologically in a QlikSense bar chart? I have loaded my data using the month() funcion, so the month names should be in QlikSense's format. None of the sorting options seems to work.

I am new to QlikSense so my apologies if this is an obvious question!

Thanks,

Erin

Re: How do I order months in a bar chart chronologically in QlikSense?

jonathandienst — Thu, 14 May 2015 05:28:08 GMT

Month() produces a dual value in the range 1 (Jan) - 12 (Dec), so the months should sort numerically, as long as the range does not span over a year end.

Re: How do I order months in a bar chart chronologically in QlikSense?

JonnyPoole — Thu, 14 May 2015 14:19:36 GMT

On the right side did you drag the Month field to be the first (top) of the list ?

Re: How do I order months in a bar chart chronologically in QlikSense?

Thu, 14 May 2015 15:38:45 GMT

By default, when you create a Bar Chart, it is sorted by the expression / measure value, creating a top-down order.

On the other hand, a Line Chart is sorted by the dimension if it contains a number (like this case, being months).

If you try to use a Bar Chart, then just go to the Sorting tab in the Properties panel to the right as Jonathan pointed out and drag the Month to the 1st place of the list (sorting criteria).

Re: How do I order months in a bar chart chronologically in QlikSense?

Thu, 14 May 2015 18:50:42 GMT

Hi Everyone,

Thanks very much for this help! It looks like my issue is that I have broken down the x-axis by year and month (as separate dimensions), which means that if I drag month to the top of my dimensions list, I lose the chronological display that I want as then the data is grouped by each month, with multiple years.

To solve this I have changed my data load to Date#(InvDate, 'MMM-YY') as Date where InvDate is the date serial number in my source code. I do not want day granularity on the x-axis.

My data now displays chronologically, but with a serial number on the x-axis instead of MMM-YYYY. Is there a way to fix this issue? I cannot see a formatting option to alter the display.

Thanks again,

Erin

Re: How do I order months in a bar chart chronologically in QlikSense?

JonnyPoole — Fri, 15 May 2015 01:01:13 GMT

HI Eric

date# is actually an interpretation function rather than a display function.

if your source data was in YYYY-MMM format you can ensure QLik reads as a date by using :

date#(datefield,'YYYY-MMM')

but this would display with default date format.

To control the output/display , wrap it with date() with the desired format

date(date#(datefield,'YYYY-MMM'),'MMM-YYYY')

in your case you may not even need date# depending if qlik is interpreting the field as a date or not.

Re: How do I order months in a bar chart chronologically in QlikSense?

Fri, 15 May 2015 22:09:01 GMT

Hi Everyone,

Thanks again for the help. I think I've solved my problem by reloading the data using a combo of the date and date# functions as Jonathan suggested. My data is coming from SQL, passed to Excel, and then loaded. Therefore, even though it appears as DD/MM/YYYY in Excel, I believe the data for date is actually stored as a serial number in my source data. (I could have this wrong as I am not an expert!)

I've used the following expressions which seems to fix the problem:

date#(date(InvDate, 'MMM-YYYY'), 'MMM-YYYY') as Date

Without the date# function wrapped around, I would get the dates in the correct format, but the bar chart would display, for example, 31 unique bars, each labelled 'May 2014' because QlikSense was reading each day as a unique value, but still formatting them as only MMM-YYYY.

Thanks again, much appreciated!

Best

Erin

Re: How do I order months in a bar chart chronologically in QlikSense?

nagarajs — Thu, 18 Jun 2015 18:04:37 GMT

worked . thank you