12 Replies Latest reply: May 14, 2018 10:43 PM by Ron Campbell RSS

    Cannot get master calendar to sort ascending

    Cherl Zehnder

      In QlikSense I've been trying to sort my master calendar by most recent date first. Everything I read says it would work but it doesn't.  I even did the following: saved to a qvd, loaded the qvd into a temporary calendar then load it sorted, still doesn't work.

       

      Any idea what i'm doing wrong?

       

      TempPolicyCalendar:
      LOAD
      *
      FROM [lib://QVD/PolicyCalendar.qvd](qvd) ;


      PolicyCalendar:
        Load  *
        Resident tempPolicyCalendar Order By "Policy Date" DESC;

        Drop table tempPolicyCalendar;

        • Re: Cannot get master calendar to sort ascending
          Kaushik Solanki

          Hi

           

          Make sure that the date is a numeric column.

           

          Just add the date filter on sheet, if the values are on  your right then its a numeric and if not then its text.

           

          Regards,

          Kaushik Solanki

          • Re: Cannot get master calendar to sort ascending
            Marcus Sommer

            Try it with:

             

            TempPolicyCalendar:
            LOAD
            *
            FROM [lib://QVD/PolicyCalendar.qvd](qvd) ;


            PolicyCalendar:
            Noconcatenate  Load  *
              Resident tempPolicyCalendar Order By "Policy Date" DESC;

              Drop table tempPolicyCalendar;

             

            to create an independent table which is not automatically added to the temp-table.

             

            - Marcus

            • Re: Cannot get master calendar to sort ascending
              Mark Little

              Hi,

               

              Most likely treating the Date as a string and not a Date,

              Wrap your date in the DATE#().

               

              i..e DATE#(Date,'DD/MM/YYYY') as Date.

               

              But matching your field name and format.

              Also as suggested earlier make a date number field then rules than out

               

              NUM(DATE#(Date,'DD/MM/YYYY')) as DateNo

               

              Mark

                • Re: Cannot get master calendar to sort ascending
                  Cherl Zehnder

                  I've been playing with this and getting odd results. Based on the below resolts it doesn't look like I need to reformat the Valuation ME date2 with the DATE# as that exactly matches the format of the Valuation ME Date.  I do find it interesting that I only get DateNO populated on some records.  Any idea why?

                   

                  Also, based on the way it's displaying in the QVD file it appears that it is actually sorted descending when it saves to QVD.  However, when I use that QVD in another application I cannot get it to display descending no matter which record below I try to sort on.

                   

                   

                  Here is the code

                  TempCalendar:
                  Load
                  $(varMinDate) + Iterno()-1 as Num,
                  Date($(varMinDate)+Iterno()-1) as TempDate
                  AutoGenerate 1 While $(varMinDate)+IterNo()-1 <= $(varMaxDate);

                  ValuationCalendar:
                  Load
                  TempDate as "Valuation Date",
                  Date(Monthend(TempDate)) AS "Valuation ME Date",
                  DATE#(TempDate,'DD/MM/YYYY') as "Valuation ME Date2",
                  NUM(DATE#(TempDate,'DD/MM/YYYY')) as DateNo,
                  ApplyMap('QuartersMap',month(TempDate),Null()) as "Valuation Quarter"
                  Resident TempCalendar
                  Order By TempDate DESC;
                  Drop Table TempCalendar;

                  Store ValuationCalendar into [lib://QVD/ValuationCalendar.qvd](qvd);

                   

                  The results

                  Capture.PNG

                    • Re: Cannot get master calendar to sort ascending
                      Marcus Sommer

                      Try it in this way:

                       

                      TempCalendar:
                      Load $(varMinDate) + Iterno()-1 as Num
                      AutoGenerate 1 While $(varMinDate)+IterNo()-1 <= $(varMaxDate);

                       

                      ValuationCalendar:

                      Load

                      Date(Num) as "Valuation Date",

                      Date(floor(Monthend(Num))) AS "Valuation ME Date",

                      Date(Num,'DD/MM/YYYY') as "Valuation ME Date2",

                      Num as DateNo,

                      ApplyMap('QuartersMap',month(Num),Null()) as "Valuation Quarter"

                      Resident TempCalendar  Order By TempDate DESC;

                       

                      Drop Table TempCalendar;

                       

                      and here is very good explanation about: Get the Dates Right.

                       

                      - Marcus

                      • Re: Cannot get master calendar to sort ascending
                        Sasidhar Parupudi

                        May be try

                         

                        TempCalendar:

                        Load

                        $(varMinDate) + Iterno()-1 as Num,

                        $(varMinDate)+Iterno()-1 as TempDate

                        AutoGenerate 1

                        While $(varMinDate)+IterNo()-1 <= $(varMaxDate);

                         

                         

                        ValuationCalendar:

                        Load

                        Date(TempDate) as "Valuation Date",

                        Date(Monthend(TempDate)) AS "Valuation ME Date",

                        DATE(TempDate,'DD/MM/YYYY') as "Valuation ME Date2",

                        TempDate as DateNo,

                        ApplyMap('QuartersMap',month(TempDate),Null()) as "Valuation Quarter"

                        Resident TempCalendar

                        Order By TempDate DESC;

                        Drop Table TempCalendar;

                        • Re: Cannot get master calendar to sort ascending
                          Ron Campbell

                          Hi Cherl

                          The reason you are getting only values in some lines for DateNo is you are using MM/DD/YYYY format for your dates but in that line you are usng DD/MM/YYYY in the assignment line:

                          NUM(DATE#(TempDate,'DD/MM/YYYY')) as DateNo,

                          So where the day of the month is 1-12 it is interpreting it as a date and the rest are not valid dates for that format


                          Ron