Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Help with selection of last recorded value

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.

        

IDNameDepartmentContractHoursDate of contractPayroll stateContract State
123John45Full Time4401-01-2010 0:00AA
123John45Full Time4406-01-2010 0:00AA
123John45Full Time4407-01-2011 0:00AA
123John45Full Time4412-01-2011 0:00AA
123John45Full Time4403-01-2012 0:00AA
123John45Full Time4412-01-2012 0:00AA
123John45Full Time4412-01-2012 0:00AA
123John45Full Time4406-01-2013 0:00AA
123John45Full Time4412-01-2013 0:00AA
123John45Full Time4412-01-2013 0:00AA
123John45Full Time4407-01-2014 0:00AA
123John45Full Time4412-01-2014 0:00AA
123John45Full Time4412-01-2014 0:00AA
124Adam45Full Time4401-10-1973 0:00AA
124Adam45Full Time4401-08-2010 0:00AA
124Adam45Full Time4401-11-2011 0:00AA
124Adam45Full Time4401-03-2012 0:00AA
124Adam45Full Time4419-04-2012 0:00T

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.

8 Replies
Kushal_Chawda

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

Anonymous
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

Oh. sorry