Help with selection of last recorded value

[Anonymous]

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

The data is of the employees of a Department, and what I want to show, is a graphic with the names of the employees according to a year that I select. For the example I will use only two employees: John and Adam. Both of them are Full time employees.

The graphic will show the names of all the full time employees that has a payroll and contract state active.

ID	Name	Department	Contract	Hours	Date of contract	Payroll state	Contract State
123	John	45	Full Time	44	01-01-2010 0:00	A	A
123	John	45	Full Time	44	06-01-2010 0:00	A	A
123	John	45	Full Time	44	07-01-2011 0:00	A	A
123	John	45	Full Time	44	12-01-2011 0:00	A	A
123	John	45	Full Time	44	03-01-2012 0:00	A	A
123	John	45	Full Time	44	12-01-2012 0:00	A	A
123	John	45	Full Time	44	12-01-2012 0:00	A	A
123	John	45	Full Time	44	06-01-2013 0:00	A	A
123	John	45	Full Time	44	12-01-2013 0:00	A	A
123	John	45	Full Time	44	12-01-2013 0:00	A	A
123	John	45	Full Time	44	07-01-2014 0:00	A	A
123	John	45	Full Time	44	12-01-2014 0:00	A	A
123	John	45	Full Time	44	12-01-2014 0:00	A	A
124	Adam	45	Full Time	44	01-10-1973 0:00	A	A
124	Adam	45	Full Time	44	01-08-2010 0:00	A	A
124	Adam	45	Full Time	44	01-11-2011 0:00	A	A
124	Adam	45	Full Time	44	01-03-2012 0:00	A	A
124	Adam	45	Full Time	44	19-04-2012 0:00	T	I

As you could see in the set of data, if I wanted to count the employees of the year 2010 and 2011, both Adam and John would have to be counted. But if I wanted to show the employees of the year 2012, 2013 and 2014, only John would have to be counted, becasue Adam has a terminated and inactive state of payroll and contract.

But there is also another catch, since John has a full time contract, I dont have to renew it every year, so if  I wanted to count the employees of the year 2015, even though the last date of contract of John is in 2014, he would still have to be counted for the 2015 list, because his last record hast an Active payroll state and alse an active contract state.

So far im using the following statement to do this:

if Year=2015,

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

The problem is that with that statement, I'm not counting John for the year 2015.

I hope I was clear enough and I hope you can help me.

[Kushal_Chawda]

How you are getting YearofContract? In your data I can see the data till 2014 only

[Anonymous]

I'm getting it like this:

mid(date(Date of contract),7,5) as YearofContract

[Kushal_Chawda]

Instead use

Year(Date of contract) as YearofContract

[Anonymous]

Nice, I didn't know that trick.

But i've still got troubles getting the right solution, I can't figure out yet how to show the name of John for the 2015 employees...

[sunny_talwar]

if Year=2015,

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

Do you have two Year fields? Year and YearOfContract? or do you need to change Year to YearOfContract?

[Anonymous]

I have the year (which is the year of the system) and also the year of contract, which is they year that the employee has the contract.

I understand your set moddifier, as matter of fact I'm using the same one, the problem is that, in the case of John, who is a full time employee, I don't need to renew his contract every year, so his last year of contract is 2014, but he's still is a 2015 employee because he has a full time contract and his last recorded values of payroll and contract state are still A (active)

[sunny_talwar]

The expression above is yours, I did not propose anything yet . I was just trying your expression.

[Anonymous]

Oh. sorry