Last status per Source ID for random date selection

[#1]

Hi

I am trying to get the last status for each Source ID for a random date selection in the front end (by the users).

For e.g. original data set is as follows:

Source ID	Status	Date
x	A	01/01/2014
x	B	02/01/2014
y	C	03/01/2014
x	A	06/01/2014
y	A	08/01/2014
z	B	10/01/2014
y	C	12/01/2014
x	A	18/01/2014
y	A	21/01/2014
z	D	22/01/2015
x	A	25/01/2016
y	C	27/01/2017
z	B	29/01/2018

Now, if the user selects the date range from 03/01/2014 to 22/01/2014 then on Qlikview I want to show

Source ID	Status	Date
y	A	21/01/2014
x	A	18/01/2014
z	D	22/01/2015

[MK_QSL]

Check enclosed file.

[#3]

Hi Manish

Thank you for the feedback.

One more thing, how do i get a count (collectively for all the statuses).

So assuming this was the result:

Source ID	FirstSortedValue(Status,-Date)	FirstSortedValue(Date,-Date)
x	A	25/01/2016
y	C	27/01/2017
z	B	29/01/2018
u	A	29/01/2018
v	A	27/01/2017
w	B	25/01/2016
s	B	29/01/2018
r	B	25/01/2016
t	C	27/01/2017

How do i get this:

FirstSortedValue(Status,-Date)	Count
A	3
C	2
B	4

[MK_QSL · Accepted solution]

File enclosed.