Employee current status in text box

[pavan045]

I have a table like this

EmpId	StartDate	EndDate	Status
1	30/09/2014	20/01/2015	Under Training
1	21/01/2015	2/3/2015	Buisiness Wait
1	3/3/2014	31/01/2016	In Project
2	10/3/2015	21/06/2015	Under Training
2	22/06/2015	20/09/2015	Buisiness Wait
3	27/12/2014	26/02/2015	Under Training
3	27/02/2015	16/06/2015	Buisiness Wait
3	17/07/2015	31/01/2016	In Project

From this how can I show Employee status on 31/01/2016 in a text box with out using date filter.

[sunny_talwar]

Try this:

=FirstSortedValue(Status, -EndDate)

[swuehl]

Maybe the status of EmpId is undefined after 20/09/2015, do you want to show her status then?

You can filter using set analysis:

=FirstSortedValue({<StartDate = {"<='31/01/2016'"}, EndDate = {">='31/01/2016'"}>} Status, -EndDate)

[pavan045 · Accepted solution]

Thank you for your answer sunny...

Your expression gives null values only. I just added 'DISTINCT' and it works.

Final Expression is FirstSortedValue(distinct Status, -EndDate).

Thank you.

[pavan045]

Thank you for your answer Swuehl

Please don't bother about EmpId Status after 20/09/2015.

Below expression worked for me.

FirstSortedValue(distinct Status, -EndDate)

Thank you.

[sunny_talwar]

Awesome, I am glad you were able to figure it out.

Please close the thread if you have got the answer you were looking for by marking correct and helpful answers.

Best,

Sunny