7 Replies Latest reply: Mar 28, 2014 10:04 AM by Vinay Naran RSS

    Max Month

      Hi everybody,

      I have Year Field as 2010,2011,2012 and

      Month Field as JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC

       

      For Getting Maximum Year , I have used =Max(Year). Its working fine and returns 2012

      But for getting Maximum Month , I have used =Maxstring(Month). It not returns 'DEC' , Instead it returns 'SEP'

       

      I want to get the maximum month as DEC.

       

      Kindly help me to sort out this issue.

       

      Regards,

      Sivasu

        • Re: Max Month
          Alessandro Saccone

          If you need to use months as numerical values (Max for example) I suggest you to use month as year + month so, if you have a date (myDate):

           

          Load

               weekyear(myDate)*100 + month(myDate) as month,

               monthname,

               ....

           

          In this way you will have a numerical value for months that will help to find max

           

          Hope it helps

          • Re: Max Month
            Marco Wedel

            =month(max(month))

             

            regards marco

            • Re: Max Month
              Saradhi Balla

              maxstring is sorting by alphabets...so S is the last alphabet in the month's list. so SEP is being returned.

              you need to use dual function here...straight from the help section. look up dual in help and copy that example into your script. just change the field name to match yours and use months like JAN, 1; FEB, 2; etc. then you can call max(Month) and it will return DEC.

               

              straight from help section:

               

              load dual ( string,numrep ) as DayOfWeek inline

               

              [ string,numrep

               

              Monday,0

               

              Tuesday,1

               

              Wednesday,2

               

              Thursday,3

               

              Friday,4

               

              Saturday,5

               

              Sunday,6 ];

               

              load Date, weekday(Date) as DayOfWeek from afile.csv;

               

              The script example will generate a field DayOfWeek
              with the weekdays written in clear text. QlikView will for all purposes regard
              the field as a numeric field.

               

              then use Marco's expression to get the string representation of the max month instead of the number.

              • Re: Max Month
                Suhas Gupta

                Hi Sivasu,

                 

                If this month and year are coming from some date field then instead of taking max(Month) use Month(max(date)).

                This will give you latest date. But if you want max month then load one inline table with two fields i.e. Month and Month_Number and associate with this table and then take max(Month_Number) you will get dec.

                 

                Regards

                Suhas Gupta

                • Re: Max Month
                  Manish Kachhia

                  Try below in text box...

                   

                  =SubField('$(MonthNames)', ';' , Max(Month))

                  • Re: Max Month
                    David Foster

                    If you have to have text-style months in your data then always use DUALs for Months.

                    • Re: Max Month
                      Vinay Naran

                      use

                      =month(max(month))

                       

                      you can't use maxstring because it return maximum value by alphabets  and SEP start with S.

                       

                      Vinay