Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to get last 3 values from date column

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.

3 Replies
akshayjain90
Contributor II
Contributor II

Hi,

Refer this expression -

sum({<Date = {'>$(=(date(AddMonths( date#(max(Date),'DD/MM/YYYY'),-3),'DD/MM/YYYY')))'}>}Amount)

Regards,

Akshay

MK_QSL
MVP
MVP

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)

Gysbert_Wassenaar

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;


talk is cheap, supply exceeds demand