12 Replies Latest reply: May 29, 2009 8:30 PM by John Witherspoon RSS

    Order Stacked chart by date field.

    Daniel Burrell

      Hi, I've tried to Sort Stacked chart by date field. Using "numeric Ascending"

      Date field is displayed as "May 2009".."Apr 2009"... etc..

      However it does not change it's order.

      I would like to keep the display as "MMM YYYY" but still have it sorted.

      Any help?

      Tried:

       

      =



      date(a, 'MMM YYYY')

      and also:

       

       

      =

      month(a) & ' ' & Year(a)





       

        • Order Stacked chart by date field.
          Michael Solomovich

          This shouldn't be a problem. Make sure that your field "a" is numeric (date is also numeric). Can you post an example of the problem?

            • Order Stacked chart by date field.
              Daniel Burrell

              Ok so lets look at the SQL Statement I made:

              SQL SELECT convert(date, Date, 103) as MyDate,
              ID as MyID,
              Value as MyValue
              FROM MyDatabase
              WHERE Date>= '20080101';

              First of all I'm not convinced that the WHERE clause is correct, since I havn't told it should compare Date to a date in the format YYYYMMDD.

              So if you could advise me there I would appreciate it.

              Otherwise that's how I pull the data in, and the type IS (or rather should be) a date, which is as you say numeric, yet it still doesn't order them correctly.

              Any help?

              Thanks

                • Order Stacked chart by date field.
                  John Witherspoon

                  Well, I don't know what format you're using in your database for Date. In our shop, it's usually a numeric field in the form YYYYMMDD. For the sake of argument, I'll assume your shop is the same. Then I'd do it more like this:

                   

                  LOAD

                  date(date#(Date,'YYYYMMDD'),'MM/DD/YY') as MyDate

                  ,ID as MyID

                  ,Value as MyValue

                  ;

                  SQL SELECT

                  Date

                  ,ID

                  ,Value

                  FROM MyDatabase

                  WHERE Date >= 20080101

                  ;

                   

                  The main idea, though, is that you want MyDate to be a QlikView date, which has a numeric internal representation, which means you can sort by it. I could be way off base on where your problem lies, though.

                    • Order Stacked chart by date field.
                      Daniel Burrell

                      Well the format I'm using in my database shouldn't matter since I'm using convert() to pull the date into the format I expect - 103 which is dd/mm/yy

                      But it is stored as a Date.

                      I presume when you say numeric, you mean an integer.. the old way of storing..

                      See I can get an ordering, but only if I set my X axis to display YYYY MM, because YYYYMM format will by design always be in order as an integer.

                      But this is a pathetic approach, and I would expect qlikview to distinguish between and keep separate the model or internal representation of a date to the view or display of the date.

                      I should be able to request the Days of the month to be displayed in terms of different breed of monkeys. So long as I define an ordering, qlikview should look at the internal representation of a date, order by that, and then convert it into a monkey...

                      Are you seriously telling me qlikview orders by what is displayed on the X axis and not by what is held internally?

                      =Date(HistoryScoreDateB, 'YYYY') & ' '&Date(HistoryScoreDateB, 'MM')

                      Is the only expression that works and it's all smoke and mirrors since

                      =Date(HistoryScoreDateB, 'MM') & ' '&Date(HistoryScoreDateB, 'YYYY')

                      gives me a numeric ordering, but not a date ordering.

                    • Order Stacked chart by date field.
                      Michael Solomovich

                      It's easy to see the format of your "MyDate" field in the Document properties - number.
                      And you can convert it in the the script using many ways, including that from John.

                  • Order Stacked chart by date field.
                    Michael Solomovich

                    You don't understand. First, the date is numeric but not integer. Second, even presentation like 'May-09' can be numeric in QV world... And, I recommended to check the format not in the database, but after you loaded it in QV.
                    See attched calendar example, and notice that fields Date, Month, Weekday, MonthYear - all sorted by numeric order, and can be sorted this way in a chart as well.

                    • Order Stacked chart by date field.
                      Rob Wunderlich

                       


                      Alkaline wrote:
                      =
                      date(a, 'MMM YYYY')

                       

                      I'm guessing from your first post that you are defining the dimension as a Calculated Dimension? If so, the date() expression will generate a set of strings which is not what you want. You want dual values. Two options.

                      1. Change the document format of the field to be MMM YYYY.

                      2. Create a new field in the script like:

                      date(thedate, 'MMM YYYY') as YearMonth

                      and then use the YearMonth as your dimension.

                      -Rob

                      • Order Stacked chart by date field.
                        Daniel Burrell

                        OK,

                        So the way I got round it, was to load the data in order using ORDER BY in the sql statement.

                          • Order Stacked chart by date field.
                            John Witherspoon

                            That works, but isn't "The QlikView Way". It seems to me that either we don't understand your problem, or you don't understand our solutions.

                            QlikView dates are stored internally as the number of days since 12/30/1899. But QlikView gives you lots of options for how you display the dates, including "May 2009". If you use QlikView dates rather than some other date format, you can then use all of QlikView's date manipulation functions, everything shows up ordered correctly, and life is generally easier. You should use QlikView dates.

                            Here's an example of turning numeric dates into QlikView dates and months. The first format string tells it the format of the input number, the second tells it the format you want to see in QlikView by default (can be overridden on charts). And attached is an application using this exact script, then doing a stacked bar chart by month with the months in order. So far as I understand, that's what you want. It does not require sorting the input data. It only requires using the right format for your dates.

                            LOAD
                            date(date#(Date,'YYYYMMDD'),'DD/MM/YY') as Date
                            ,date(monthstart(date#(Date,'YYYYMMDD')),'MMMM YYYY') as Month
                            ,pow(10,rand()*3) as Sales
                            ,pick(ceil(rand()*2),'Company A','Company B') as Customer
                            INLINE [
                            Date
                            20081201
                            20090522
                            20090505
                            20090413
                            20090108
                            20090220
                            20081215
                            20090221
                            20090317
                            20090316
                            ];