Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a requirement,how to get last three values from the date column.
Example:
Date
12/01/2015
13/02/2015
15/02/2015
18/04,2015
21/06/2015
01/07/2015
08/07/2015
30/09/2015
31/10/2015
21/11/2015
22/12/2015
in the above column i want get last 3 values.
Hi,
Refer this expression -
sum({<Date = {'>$(=(date(AddMonths( date#(max(Date),'DD/MM/YYYY'),-3),'DD/MM/YYYY')))'}>}Amount)
Regards,
Akshay
Temp_Data:
Load * Inline
[
Date
12/01/2015
13/02/2015
15/02/2015
18/04/2015
21/06/2015
01/07/2015
08/07/2015
30/09/2015
31/10/2015
21/11/2015
22/12/2015
];
Left Join (Temp_Data)
Load Date, RowNo() as ID Resident Temp_Data Order By Date Desc;
NoConcatenate
Final:
Load Date Resident Temp_Data
Where ID <= 3
Order By ID;
Drop Table Temp_Data;
at Front End
=SUM({<ID = {">=$(=Max(ID)-2)<=$(=Max(ID))"}>}ID)
or
=SUM({<ID = {">=$(=Max(ID)-2)<=$(=Max(ID))"}>}YourFactDataFieldName)
Temp:
LOAD * INLINE [
Date
12/01/2015
13/02/2015
15/02/2015
18/04/2015
21/06/2015
01/07/2015
08/07/2015
30/09/2015
31/10/2015
21/11/2015
22/12/2015]
;
Result:
FIRST 3
NOCONCATENATE
LOAD Date
RESIDENT Temp
ORDER BY Date desc;
DROP TABLE Temp;