Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Finding the entity for the maximum date

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 NoEmployee NameEntity nameAssignment Start DateStandard Hours
1Advani, VikramABC Australia01-Apr-1994400
1Advani, VikramABC Europe01-Apr-2004400
2Agarwal, ShashankABC Australia22-Sep-1997400
2Agarwal, ShashankABC Switzerland15-Jan-2008400
2Agarwal, ShashankABC USA01-Apr-2010400
2Agarwal, ShashankABC Australia01-Jan-2018400
3Abraham, Anil AlexABC Australia01-Jun-2001400
3Abraham, Anil AlexABC Singapore10-Apr-2018400

Expected result is below

    Expected Result

Employee NoEmployee NameEntity nameAssignment Start DateStandard Hours
1Advani, VikramABC Europe01-Apr-2004400
2Agarwal, ShashankABC Australia01-Jan-2018400
3Abraham, Anil AlexABC Singapore10-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

Tags (1)
3 Replies
Highlighted

Re: Finding the entity for the maximum date

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])

Highlighted

Re: Finding the entity for the maximum date

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

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

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Highlighted
Partner
Partner

Re: Finding the entity for the maximum date

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