2 Replies Latest reply: Nov 25, 2011 5:44 AM by david.morris RSS

    Order Text Representing Month and Year in Chronological Order

      Hello,

       

      I have data that represents dates coming out of the database as strings (and stored as a varchar in the database). I am formatting this string on the load statement and get the format I want showing month and year (i.e. Apr-2006). I have this in a qlikview listbox, but now want to order this text in chronological order. I can't seem to find a way. I know to use the properties>sort, but am coming unstuck on an expression that might solve this.

       

      LOAD "CRN",

           If(mid(DISCHARGEDATE,5,2)='01','Jan' & '-' & left(DISCHARGEDATE,4),

           If(mid(DISCHARGEDATE,5,2)='02','Feb' & '-' & left(DISCHARGEDATE,4),

           If(mid(DISCHARGEDATE,5,2)='03','Mar' & '-' & left(DISCHARGEDATE,4),

           If(mid(DISCHARGEDATE,5,2)='04','Apr' & '-' & left(DISCHARGEDATE,4),

           If(mid(DISCHARGEDATE,5,2)='05','May' & '-' & left(DISCHARGEDATE,4),

           If(mid(DISCHARGEDATE,5,2)='06','Jun' & '-' & left(DISCHARGEDATE,4),

           If(mid(DISCHARGEDATE,5,2)='07','Jul' & '-' & left(DISCHARGEDATE,4),

           If(mid(DISCHARGEDATE,5,2)='08','Aug' & '-' & left(DISCHARGEDATE,4),

           If(mid(DISCHARGEDATE,5,2)='09','Sep' & '-' & left(DISCHARGEDATE,4),

           If(mi(DISCHARGEDATE,5,2)='10','Oct' & '-' & left(DISCHARGEDATE,4),

           If(mid(DISCHARGEDATE,5,2)='11','Nov' & '-' & left(DISCHARGEDATE,4),

           If(mid(DISCHARGEDATE,5,2)='12','Dec' & '-' & left(DISCHARGEDATE,4))))))))))))) as DisMonth

       

      Any advise would be greatfully received??

       

       

       

      Thanks

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      ,