Last day of month Value

[pauledrich]

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

[Sokkorn]

Hi,

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

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