Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
Try this:
=FirstSortedValue(Status, -EndDate)
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)
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.
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.
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