Help - last day of Month

[pauledrich]

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.

[kaushiknsolanki · Accepted solution]

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

[pauledrich]

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

[pauledrich]

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