Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Bar graph in date order

HI

I'm using Qlik sense desktop and am having an issue getting a Graph to show in date order.

I'm importing an excel that looks something like this

Header 1Header 2Header 3Header 4
OCT 14Nov 14Dec 14

Project 1

100
Project 2400
Project 3100

I'm trying to create a bar chart showing in Month order (oct,14, nov14, dec14)

The actual excel contains the whole year.

In excel these fields are not Date fields but general

My LOAD looks like this

CrossTable(Month,value)
LOAD
    Status,
Date(Date#("Oct 14", 'MMM YY')) AS Date,
    "Oct 14",
Date(Date#("Nov 14", 'MMM YY')) AS Date,
    "Nov 14",
Date(Date#("Dec 14", 'MMM YY')) AS Date,
    "Dec 14",
    "Jan 15",
    "Feb 15",
    "Mar 15",
    "Apr 15",
    "May 15",
    "Jun 15",
    "Jul 15",
    "Aug 15",
Date(Date#("Sep 15", 'MMM YY')) AS Date,
    "Sep 15"

I can create the graph using a Dimension of Month and a Measure of Value.

The graph number are correct but they order not in month order. (screen shot below)

I do notice that in the sorting menu no matter what options I set the graph never re orders when sorting with the month field. Only if I change sort options on the value field

Can anyone help sure am I missing something simple

Cheers

4 Replies
Not applicable

Re: Bar graph in date order

As your columns are Text not date they are sorted alphabetically.

You may do two things,

1. Load your columns as Date

2. Add a new column to your load script which defines the sort order  (e.g Oct-14 being 1 and Nov-14 is 2 and so on)

     then you can modify the sort order of the chart by changing the sorting attribute of date to Custom and selecting      expression and then adding your sort order column to the expression.

Not applicable

Re: Bar graph in date order

Thanks for the reply,

Sorry new to Qlik, can you explain how I can add a column to the load script which defines the sort order?

I'm on Click sense desktop

Thanks

Not applicable

Re: Bar graph in date order

You can do an Inline Load

such as


DateOrder:

LOAD * INLINE [

   Date, SortOrder

  Oct 14, 1

  Nov 14, 2

  Dec 14, 3

];

Assuming the field name of you date filed is "Date" this should create you a table with months sorted by SortOrder column. You can then use that column in your sort expression of the dimension

Not applicable

Re: Bar graph in date order

First Load the Cross table after that apply date formatting to Month field.

Temp:

Cross Table( Month , Value)

Load

  ProjectID ,

  [Oct 14] ,

  [Nov 14],

.

.

.

.

Final:

No Concatenate

LOAD

     ProjectID ,

     Date(Date#(Month,'MMM YY')) AS Month_Name,

     Value

Resident Temp;

Drop Table Temp;