Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Not applicable

AGGR to make a Dimension an Expression

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.

Tags (1)
1 Solution

Accepted Solutions
Highlighted
evan_kurowski
Valued Contributor

Re: AGGR to make a Dimension an Expression

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

View solution in original post

7 Replies
Highlighted

Re: AGGR to make a Dimension an Expression

Can you share a sample with your expected output?

Highlighted
evan_kurowski
Valued Contributor

Re: AGGR to make a Dimension an Expression

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

View solution in original post

Highlighted
SreeniJD
Valued Contributor

Re: AGGR to make a Dimension an Expression

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

Highlighted
MVP
MVP

Re: AGGR to make a Dimension an Expression

Take a straight table.

Dimension: Employee

Exp: FirstSortedValue( TitleField, - DateAssociated)

Highlighted
Not applicable

Re: AGGR to make a Dimension an Expression

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

Highlighted
Not applicable

Re: AGGR to make a Dimension an Expression

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

Highlighted
evan_kurowski
Valued Contributor

Re: AGGR to make a Dimension an Expression

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.

   

DateTitle
1/1/2015Gopher
1/1/2015Lackey

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

   

DateTitle
1/1/2015Gopher
1/1/2015Gopher

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.