topic Re: How to get last 3 values from date column in QlikView

How to get last 3 values from date column

Thu, 24 Dec 2015 05:46:14 GMT

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.

Re: How to get last 3 values from date column

akshayjain90 — Thu, 24 Dec 2015 07:52:40 GMT

Hi,

Refer this expression -

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

Regards,

Akshay

Re: How to get last 3 values from date column

MK_QSL — Thu, 24 Dec 2015 08:03:13 GMT

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)

=SUM({<ID = {">=$(=Max(ID)-2)<=$(=Max(ID))"}>}YourFactDataFieldName)

Re: How to get last 3 values from date column

Gysbert_Wassenaar — Sun, 10 Jan 2016 10:43:23 GMT

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;