Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
New Contributor II

Re: How to get last 3 values from date column

Hi,

Refer this expression -

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

Regards,

Akshay

MVP
MVP

Re: How to get last 3 values from date column

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)

MVP & Luminary
MVP & Luminary

Re: How to get last 3 values from date column

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