AGGR to make a Dimension an Expression

[#1]

Does anyone know how to use aggr and make an expression a dimension?  I'm running into a problem where I have people (Employee) with multiple titles (Title) and I only want to associate them with the most recent one.

[sunny_talwar]

Can you share a sample with your expected output?

[evan_kurowski · Accepted solution]

Hello eyceman77

You could start with something like this:

=Aggr(FirstSortedValue(Title,Date_Title_Granted),Employee)

//negate -Date_Title_Granted to switch between first/last as needed

[SreeniJD]

Dear eyceman77

Two ways to achieve this..

Capture the effective from date and end date for each title/position employee has

if end date > current date or null then its active title/recent tile..

you can also, maintain a log of whether the Title active or deactivated by querying such that if end date is null then its active otherwise deactivated..

Evan's expression will work perfect.. if you want to use in frontend.

HTH,

Sreeni

[tresesco]

Take a straight table.

Dimension: Employee

Exp: FirstSortedValue( TitleField, - DateAssociated)

[#6]

Thanks everyone, AGGR(FirstSortedValue()) was just what I was looking for!

[#7]

Any idea why switching this from first to last with -[Date Field] would produce a null, but work the other way around?

[evan_kurowski]

eyceman77 <span class="icon-status-icon icon-partner" title="Partner"></span> wrote:

Any idea why switching this from first to last with -[Date Field] would produce a null, but work the other way around?

Hello eyceman77, can't be certain for sure without seeing, but most often I start getting Null as the result of FirstSortedValue()  when it has more than one possible row returned (and I mean row, not just distinct value).

To be specific, if the first sorted value date were 1/1/2015 and there were two titles granted, 'Lackey' & 'Gopher', then FirstSortedValue() would return null.

Date	Title
1/1/2015	Gopher
1/1/2015	Lackey

But i have had instances where even if there are two rows that are identical in the data model, surprisingly even though it would pass an Only() evaluation, still returns null as FirstSortedValue()

Date	Title
1/1/2015	Gopher
1/1/2015	Gopher

If you're certain that you're down to a single result row, and it is still not working, then maybe look to other syntax in play.