Max date

[#1]

Hi,

I have data as below

Emp     Date                    Sal     Grade

ABC     01/27/2012          100     A

ABC     02/27/2012          200     B

DEF     3/31/2012            100     B

DEF      5/31/2012          400     C

My requirement is I have to pick Grades and salaries of only latest date for each employee and show it in pie chart...

so ideally I should only pick salaries and grades of employee of ABC Dated 2/27/12  and 5/31/2012 for DEF

currently Im using below formula in pie chart..

sum(salary)/sum(total slalary) so this forumla picks salaries for all records..I want this to pick only salaries and grades of recent dates for each emp and do the calcuation

any help is highly appreciated..

[nagaiank]

The script below will work. The table Tab2 has the salary and grades of employees for the latest date.

Tab1:

LOAD * Inline [

Emp,Date,Sal,Grade

ABC,01/27/2012,100,A

ABC,02/27/2012,200,B

DEF,3/31/2012,100,B

DEF,5/31/2012,400,C

];

Tab2:

load Emp

          , FirstSortedValue(Date,-Date) as LastDate

          , FirstSortedValue(Sal,-Date) as LastSal

          , FirstSortedValue(Grade,-Date) as LastGrade

          Resident Tab1

          Group By Emp;

[#3]

appreciate your response but there are 50 different columns like salary..there are some complex conditions that are used for few graphs..I want to put this condition in the expression of the graph rather than in the script any help

[nagaiank]

The FirstSortedValue function works in chart expression also.

So, if you create a pie chart with the following dimension and expression, the chart will plot using the latest salary of employees:

Dimension: Emp

Expression: Aggr(FirstSortedValue(Sal,-[Date]),Emp)

This works.

[sujeetsingh]

Why not to use a canditional expression...

only({$<Dtae={$(min(interval(Today-Dta,d)))}>}

[#6]

Hi krishnamoorthy,

Appreciate your help.

what would be the expression if I need percentage of latest Grades for employee..

currently Ihave Grade in dimension and expression I have count(emp)/count(total emp)...now I need to pick only latest grades in the dimension instead of all grades..

please help..

[#7]

hi this did not work..there was no change in data..

thanks for the help...

[rwunderlich]

You don't need to do division to get the relative percentage, use the "Relative" expression property instead.

Dim: Emp

Expression: FirstSortedValue(Sal, -Date)

Check the "Relative" checkbox on the expression.

-Rob

[#9]

Hi Rob,

I need the count of employees  based on latest grade..each employee can have multiple grades I need to consider only grades based on recent dates..

thanks for the help...

[rwunderlich]

"I need to consider only grades based on recent dates.".

That's what the ForstSortedValue() does. It includes only Sal from the latest Date for each employee. Isn't that what you are trying to do?

FirstSortedValue(Sal, -Date)