1 Reply Latest reply: Jul 23, 2013 9:24 PM by Sokkorn Cheav RSS

    Last day of month Value

    Paul Edrich

      Hi, I have an issue which did return the value I needed however- when the last day of the month falls on a Sunday the return value is zero - how can I amend the script below to return the previous days value when the value is zero and also retain the LastDay returned value?

       

      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

        • Re: Last day of month Value
          Sokkorn Cheav

          Hi,

           

          First of all you need to set day name in script like below

          Untitled.png

          Then use script below

          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,
             
             If(WeekDay(MonthEnd(Date))='Sun',0,Value)                        AS [LastDay2],
             If(WeekDay(MonthEnd(Date))='Sun',Previous(Value),Value)    AS [LastDay3];
          From .........
          

          Regards,

          Sokkorn