3 Replies Latest reply: Jul 22, 2013 7:57 PM by Paul Edrich RSS

    Help - last day of Month

    Paul Edrich

      Hi - I have read various solutions to this problem but have now exhausted every avenue. I have 3 colums "Location, Value, Date", I want to return value for the last date of each month.

       

      My script is

      "Load

      Location,
        
      Value,
         
      Date (floor(Date)),'DD/MM/YYYY' as Date,
         
      Date,
        
      Day (Date) as Day,
        
      MonthEnd (Date)as MonthEnd,
        
      MonthName (Date) as Month,
        
      Month (Date) as Month1,
        
      Year (Date) as Year,
        
      IF(Date(SummaryDate,'DD/MM/YYYY') = Date(Floor(MonthEnd(SummaryDate)),'DD/MM/YYYY'),1,0) as LastDay; "

      I was hoping that the above would return a value of 1 but it is only returning a value of 0?Any solutions would really help. many thanks.

        • Re: Help - last day of Month
          Kaushik Solanki

          Hi,

           

               Try this.

           

          Load

             Location,
             Value,
             Date (floor(Date)),'DD/MM/YYYY' as Date,
             Date,
             Day (Date) as Day,
             MonthEnd (Date)as MonthEnd,
             MonthName (Date) as Month,
             Month (Date) as Month1,
             Year (Date) as Year,
             IF(Floor(SummaryDate) = Floor(MonthEnd(SummaryDate)),Value,0) as LastDay
          From ......

           

               I hope SummaryDate is coming from same table.

           

          Regards,

          Kaushik Solanki

            • Re: Help - last day of Month
              Paul Edrich

              Thank you - very helpfull I now see where I was going wrong. Much appreciated.

                • Re: Help - last day of Month
                  Paul Edrich

                  Hi, I have an issue - when the last day of the month falls on a Sunday the return value is zero - how can I amend the script to return the previous days value when the value is zero?

                   

                  The script is:-

                   

                  Load
                    
                  Location,

                    
                  Value,

                    
                  Date (floor(Date)),'DD/MM/YYYY' as LastDate,

                    
                  Date,

                    
                  Day (Date) as Day,

                    
                  MonthEnd (Date)as MonthEnd,

                    
                  MonthStart (Date) as FirstDay,

                    
                  MonthName (Date) as Month,

                    
                  Month (Date) as Month1,

                    
                  Year (Date) as Year,

                    
                  IF(Floor(Date) = Floor(MonthEnd(Date)),value,0) as LastDay;

                   

                  From .........

                   

                  Thank you if anyone can help