How to get the previous and current rating for an employee

[Anonymous]

Employee_ID	AppraisalToDate	Final_Rating
100	6/1/2014	4
101	6/1/2014	5
102	6/1/2014	3
103	6/1/2014	2
100	12/1/2014	5
101	12/1/2014	4
102	12/1/2014	3
103	12/1/2014	2
100	6/1/2015	5
101	6/1/2015	4
102	6/1/2015	3
103	6/1/2015	2
100	12/1/2015	5
101	12/1/2015	4
102	12/1/2015	3
103	12/1/2015	2

These are the data  i have my problem is i want to get the current rating and previous rating from final rating field based on the appraisalto date

below expression gives the current rating for each employee, but I did not get the previous rating,

Sum(If(Aggr(NODISTINCT MaxString(AppraisalToDate),Employee_ID) = AppraisalToDate, FinalRating))   

so,I need  how to get the previous and current rating from the above table

[sunny_talwar]

Where are you trying to get this? Is AppraisalToDate is one of the dimensions?

[maxgro · Accepted solution]

I think you can make a chart with

Employee_ID as dimension

and as expression

     FirstSortedValue(Final_Rating, -AppraisalToDate)

or for previous rating add the third parameter to FirstSortedValue function

     FirstSortedValue(Final_Rating, -AppraisalToDate, 2)

[Anonymous]

I am trying to get two columns as previous rating and current rating from single fields named as "Final_Rating" for that another column need for checking the date I used as "AppraisalToDate" column. AppraisalToDate is not a dimension Both previous rating and current rating are used as measures.

[sunny_talwar]

I think maxgro‌'s solution is what you need

[Anonymous]

thanks@maxgro 

[yoganantha321]

Whether the two fields can be used as columns in table

[rajaqliks]

sum({<AppraisalToDate={'6/1/2014'}>}Final_Rating);

sum({<AppraisalToDate={'12/1/2014'}>}Final_Rating);

sum({<AppraisalToDate={'6/1/2015'}>}Final_Rating);

sum({<AppraisalToDate={'12/1/2015'}>}Final_Rating);

try this, add in expression tab, i hope it works.

[rajaqliks]

its working but some problem is there if we can modify the date it will not working properly