7 Replies Latest reply: Mar 24, 2014 1:40 AM by vishal waghole RSS

    How to flag last day of the month

      Dear sirs, I have read how to use monthend() function in the forum, and applied the same in my qvw, but i am getting a zero value in place of 1, kindly let me know where i am mistaking.

      I have created a simple Excel sheet as shown below, and expecting last day of month's value i.e. 30000000. i am using following statement in my script

      IF(Date(Date,'DD/MM/YYYY') = Date(Floor(MonthEnd(Date)),'DD/MM/YYYY'),1,0)AS Monthend_flag,

      DateINV_VALUE
      01/01/201450000000
      02/01/201420000000
      03/01/201450000000
      04/01/201420000000
      05/01/201460000000
      06/01/201490000000
      07/01/201460000000
      08/01/201490000000
      09/01/201440000000
      10/01/201430000000
      11/01/201430000000
      12/01/201430000000
      13/01/201430000000
      14/01/201430000000
      15/01/201430000000
      16/01/201430000000
      17/01/201430000000
      18/01/201430000000
      19/01/201430000000
      20/01/201430000000
      21/01/201430000000
      22/01/201430000000
      23/01/201430000000
      24/01/201430000000
      25/01/201430000000
      26/01/201430000000
      27/01/201430000000
      28/01/201430000000
      29/01/201430000000
      30/01/201430000000
      31/01/201430000000
        • Re: How to flag last day of the month
          Marco Wedel

          Hi,

          Try


          IF(Date#(Date,'DD/MM/YYYY') = Date(Floor(MonthEnd(Date#(Date,'DD/MM/YYYY')))),1,0)AS Monthend_flag,


          Instead


          Regards


          Marco

            • Re: How to flag last day of the month
              Marco Wedel

              formating problems on my mobile

               

               

                • Re: Re: How to flag last day of the month
                  Marco Wedel

                  provided you set your date format correctly

                   

                  SET DateFormat='DD/MM/YYYY';
                  
                  
                  
                  

                   

                  you can simplifiy the expression to

                   

                  IF(Date = Floor(MonthEnd(Date)),1,0) AS Monthend_flag;
                  
                  
                  
                  

                   

                  QlikCommunity_Thread_111597_Pic1.JPG.jpg

                   

                  SET DateFormat='DD/MM/YYYY';
                  
                  LOAD
                    *,
                  // IF(Date(Date,'DD/MM/YYYY') = Date(Floor(MonthEnd(Date)),'DD/MM/YYYY'),1,0)AS Monthend_flag;          // original post
                  // IF(Date#(Date,'DD/MM/YYYY') = Date(Floor(MonthEnd(Date#(Date,'DD/MM/YYYY')))),1,0)AS Monthend_flag;  // solution http://community.qlik.com/message/493411#493411
                    IF(Date = Floor(MonthEnd(Date)),1,0) AS Monthend_flag;                                                // solution http://community.qlik.com/message/493489#493489
                  LOAD Date, 
                       INV_VALUE
                  FROM
                  [http://community.qlik.com/thread/111597]
                  (html, codepage is 1252, embedded labels, table is @1);
                  

                   

                   

                  regards

                   

                  Marco

              • Re: How to flag last day of the month
                Massimo Grossi

                IF(floor(Date(Date,'DD/MM/YYYY')) = Floor(MonthEnd(date(Date,'DD/MM/YYYY'))),1,0) AS Monthend_flag;

                • Re: How to flag last day of the month
                  Sunil Chauhan

                  see the attached file

                   

                  code for you refrence

                   

                   

                   

                  Tab1:

                  Load * inline [

                  Date, INV_VALUE

                  01/01/2014, 50000000

                  02/01/2014, 20000000

                  03/01/2014, 50000000

                  04/01/2014, 20000000

                  05/01/2014, 60000000

                  06/01/2014, 90000000

                  07/01/2014, 60000000

                  08/01/2014, 90000000

                  09/01/2014, 40000000

                  10/01/2014, 30000000

                  11/01/2014, 30000000

                  12/01/2014, 30000000

                  13/01/2014, 30000000

                  14/01/2014, 30000000

                  15/01/2014, 30000000

                  16/01/2014,30000000

                  17/01/2014, 30000000

                  18/01/2014, 30000000

                  19/01/2014, 30000000

                  20/01/2014, 30000000

                  21/01/2014, 30000000

                  22/01/2014, 30000000

                  23/01/2014, 30000000

                  24/01/2014, 30000000

                  25/01/2014, 30000000

                  26/01/2014, 30000000

                  27/01/2014, 30000000

                  28/01/2014, 30000000

                  29/01/2014, 30000000

                  30/01/2014, 30000000

                  31/01/2014, 30000000

                  ];

                   

                   

                  Load *,

                  if(trim(Date1)=Trim(Date(MonthEnd(Date1),'DD/MM/YYYY')),1,0) as monthendFlag;

                  Load *,Date#(Date,'DD/MM/YYYY') as Date1 resident Tab1;

                  drop table Tab1;

                   

                   

                  hope this helps

                  • Re: How to flag last day of the month
                    Anand Chouhan

                    Hi,

                     

                    Load some thing like

                     

                    Temp:

                    load Date#(Date,'DD/MM/YYYY') as Date;

                    LOAD * Inline

                    [

                    Date, INV_VALUE

                    01/01/2014, 50000000

                    02/01/2014, 20000000

                    03/01/2014, 50000000

                    04/01/2014, 20000000

                    05/01/2014, 60000000

                    06/01/2014, 90000000

                    07/01/2014, 60000000

                    08/01/2014, 90000000

                    09/01/2014, 40000000

                    10/01/2014, 30000000

                    11/01/2014, 30000000

                    12/01/2014, 30000000

                    13/01/2014, 30000000

                    14/01/2014, 30000000

                    15/01/2014, 30000000

                    16/01/2014, 30000000

                    17/01/2014, 30000000

                    18/01/2014, 30000000

                    19/01/2014, 30000000

                    20/01/2014, 30000000

                    21/01/2014, 30000000

                    22/01/2014, 30000000

                    23/01/2014, 30000000

                    24/01/2014, 30000000

                    25/01/2014, 30000000

                    26/01/2014, 30000000

                    27/01/2014, 30000000

                    28/01/2014, 30000000

                    29/01/2014, 30000000

                    30/01/2014, 30000000

                    31/01/2014, 30000000

                    ];

                     

                    LOAD

                    *,

                    IF(Date(Date,'DD/MM/YYYY') = Date(Floor(MonthEnd(Date)),'DD/MM/YYYY'),1,0)AS Monthend_flag

                    Resident Temp;

                    DROP Table Temp;

                     

                    Hope this helps

                    Thanks & Regards

                    • Re: How to flag last day of the month
                      vishal waghole

                      Hello Abhay,

                       

                      Try with this expression,

                       

                      if(Date(DateField,'DD/MM/YYYY') = Date(Floor(MonthEnd(Date(DateField,'DD/MM/YYYY')))),1,0) as monthend_flag

                       

                      and also find attachment which will help you.

                       

                      - Regards,

                      Vishal Waghole