4 Replies Latest reply: Feb 13, 2015 11:34 AM by Srikanth P RSS

    Bar graph in date order

    michael creasey

      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

        • 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.

          • Re: Bar graph in date order
            Srikanth P

            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;