Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have data like this:
Value date
120 1/1/2013
12 31/1/2013
122 31/1/2013
123 1/2/2013
132 15/2/2013
234 30/2/2013
13 30/2/2013
144 30/2/2013
126 1/3/2013
13 31/3/2013
313 31/3/2013..
Now i need to create a field in back end which shows me values ie.. 122, 144,313. which are the last value of the month.Please help me ...
hi
try this
LOAD
LastValue(Value) as lastvalue,
month
Group by month;
tab1:
LOAD *, date(Date#(date,'DD/MM/YYYY'),'DD-MM-YYYY') as date_new ,
month(date(Date#(date,'DD/MM/YYYY'),'DD-MM-YYYY')) as month
INLINE [
Value, date
120, 1/1/2013
12, 31/1/2013
122, 31/1/2013
123, 1/2/2013
132, 15/2/2013
234, 28/2/2013
13, 28/2/2013
144, 28/2/2013
126, 1/3/2013
13, 31/3/2013
313, 31/3/2013
] ;
hi
the month of feb either 28 days or 29 days.
Do Group by Year too for Different years
Use function
floor(MonthEnd(date)) as Monthend_Date in the script
you will get the month end dates and data in the background.