Problem showing last recorded value with conditions

[Anonymous]

Dear community, I have a problem that I hope you can help me with.

I have the following set of data from the resources Department. This data shows the information of contracts of the employees.

ID	Name	Department	Contract	Hours	Date of Contract	Payroll state	contract state
123	John	45	Full Time	44	09-01-1997 00:00:00	A	A
123	John	45	Full Time	44	12-31-2005 00:00:00	T	I
123	John	45	Full Time	44	01-01-2006 00:00:00	A	A
123	John	45	Full Time	44	07-01-2009 00:00:00	A	A
123	John	45	Full Time	44	10-01-2009 00:00:00	A	A
123	John	45	Full Time	44	12-01-2009 00:00:00	A	A
123	John	45	Full Time	44	01-01-2010 00:00:00	A	A
123	John	45	Full Time	44	06-01-2010 00:00:00	A	A
123	John	45	Full Time	44	07-01-2011 00:00:00	A	A
123	John	45	Full Time	44	12-01-2011 00:00:00	A	A
123	John	45	Full Time	44	03-01-2012 00:00:00	A	A
123	John	45	Full Time	44	12-01-2012 00:00:00	A	A
123	John	45	Full Time	44	12-01-2012 00:00:00	A	A
123	John	45	Full Time	44	06-01-2013 00:00:00	A	A
123	John	45	Full Time	44	12-01-2013 00:00:00	A	A
123	John	45	Full Time	44	12-01-2013 00:00:00	A	A
123	John	45	Full Time	44	07-01-2014 00:00:00	A	A
123	John	45	Full Time	44	12-01-2014 00:00:00	A	A
123	John	45	Full Time	44	12-01-2014 00:00:00	A	A

As you can see in the data, John has a lot of contract renewal, the last one has been on 2014, but since his payroll state and his contract state are both active (A), and also he's a full time employee, for this year (2015) I don't need to renew his contract.

The problem that i'm having, is that when I want to show a list of employees for the year 2015, It should show John on the list, because even though his last contract was on the year 2014, he's a full time employee and also has his contract and payroll state both active.

If I wanted to show the employees of the year 2013, I should check the last recorded value of john before the 2013, when he has a full time contract and both payroll and contract state are active.

I hope you can help me with that.

[Gysbert_Wassenaar]

See attached example.

[Anonymous]

It works, but I can't still implement on my code because it's not the date that I want to show, I want to show the name of the employee that as a condition must have that the last recorded value of his contract has a state of payroll and state of contract active

[Gysbert_Wassenaar]

Then use the employee name as dimension and hide the expression column.

[Anonymous]

I didn't explain myself really well.

I'm trying to show this on a bar chart that has as a dimension  a drill down group with the Departments of the company and the names of the employees and another dimension asthe years, For the expression it has only the employees that has full time contracts. So the expression soy far it's like this.

if Year=2015,

count ({< YearOfContract={2015}, payroll_state={'A'}, contract_state={'A'}, contract='Full Time' >} Distinct ID)

That's when I have the problem with John, because his last YearOfContract is 2014, but he still is an employee in 2015 becuase he has a full time contract and his payroll and contract state are active.

[Gysbert_Wassenaar]

Perhaps this blog post helps: Creating Reference Dates for Intervals