Finding the entity for the maximum date

[sharankv]

Hello friends,

I've a requirement where the employee is aligned to multiple entities over a period of time, but I need to show up only the entity he is into the latest date. Sample data is below.

  Sample data 

Employee No	Employee Name	Entity name	Assignment Start Date	Standard Hours
1	Advani, Vikram	ABC Australia	01-Apr-1994	400
1	Advani, Vikram	ABC Europe	01-Apr-2004	400
2	Agarwal, Shashank	ABC Australia	22-Sep-1997	400
2	Agarwal, Shashank	ABC Switzerland	15-Jan-2008	400
2	Agarwal, Shashank	ABC USA	01-Apr-2010	400
2	Agarwal, Shashank	ABC Australia	01-Jan-2018	400
3	Abraham, Anil Alex	ABC Australia	01-Jun-2001	400
3	Abraham, Anil Alex	ABC Singapore	10-Apr-2018	400

Expected result is below

    Expected Result

Employee No	Employee Name	Entity name	Assignment Start Date	Standard Hours
1	Advani, Vikram	ABC Europe	01-Apr-2004	400
2	Agarwal, Shashank	ABC Australia	01-Jan-2018	400
3	Abraham, Anil Alex	ABC Singapore	10-Apr-2018	400

Please note this needs to be achieved at chart level. Also the fields till assignment date should be as dimensions whereas the standard hours is the measure.

Thanks in advance

Best regards

Sharan

[sunny_talwar]

May be try this

Dimension

Employee No

Employee Name

Expression

FirstSortedValue([Entity name], -[Assignment Start Date])

Max([Assignment Start Date])

FirstSortedValue([Standard Hours], -[Assignment Start Date])

Dimensions

Employee No

Employee Name

Aggr(FirstSortedValue([Entity name], -[Assignment Start Date]), [Employee No])

Date(Aggr(Max([Assignment Start Date]), [Employee No]))

Expression

FirstSortedValue([Standard Hours], -[Assignment Start Date])

[PrashantSangle]

any specific reason behind assignment start date as dimension???

you can use max() if you want to use it as measure..

Regards,

[Ralf_Heukäufer]

Hello Sharan,

you can try it with aggregation:

https://help.qlik.com/de-DE/qlikview/12.0/Subsystems/Client/Content/ChartFunctions/aggr.htm

For example something like aggr(max([Assignment Start Date]),[Employee Name])

​as Dimension