Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
Thank you - very helpfull I now see where I was going wrong. Much appreciated.
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