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.
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;
Temp_Data:
Load * Inline
[
18/04/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 [
22/12/2015]
;
Result:
FIRST 3
NOCONCATENATE
LOAD Date
RESIDENT Temp
ORDER BY Date desc;
DROP TABLE Temp;
