Problem with Max Function

[Anonymous]

Hello Community. I have a problem with the max function that I hope you can help me with:

I have the following data:

Seller	IDSeller	ContractID	Department	Year
John	1	1	A	2010
Tom	2	2	B	2011
Joe	3	3	C	2012
Sam	4	4	D	2013
John	1	5	E	2014

What I want to do, is only show the most recent data for each Seller, this means that the Seller John shuld only appear once with the data from the most recent year (2014). So the data should look like this:

Seller	IDSeller	ContractID	Department	Year
Tom	2	2	B	2011
Joe	3	3	C	2012
Sam	4	4	D	2013
John	1	5	E	2014

Hope you can help me

[Clever_Anjos]

Use

FirstSortedValue(Department,-Year)

FirstSortedValue(ContractID,-Year)

[maximiliano_vel]

LOAD

     Seller,

     IDSeller,

     FirstSortedValue(ContractID, -Year) as ContractID,

     FirstSortedValue(Department, -Year) as Department,

     FirstSortedValue(Year, -Year) as Year

FROM [Contracts.xlsx]

(ooxml, embedded labels, table is Sheet1)

GROUP BY Seller, IDSeller;

[jagan]

Hi,

Try like this

Data:

LOAD

*

FROM DataSource;

LEFT JOIN(Data)

LOAD

IDSeller,

Max(ContractID) AS ContractID,

1 AS LatestFlag

RESIDENT Data;

Now in chart try like this

Chart : Straight Table

Dimension:Seller,IDSeller,ContractID,Department

Expression: Only({<LatestFlag={1}>} Year)

Hope this helps you.

Regards,

jagan.

[sasiparupudi1]

Hi

Please check the attached file. hope this helps

Sasi