How to get the last value per group

[#1]

Hi

First of all sorry for my bad english i hope you can understandme.

I'm loading 3 tables in qliksense

Projects

(Identity)

IdProject   ProjectName

1               Project1

2               Project2

...               .....  

N               ProjectN

Status

(Identity)

IdStatus   StatusName

1               OnTime

2               Paused

3               Delayed

4               Other

ProjectStatusHistory

(Identity)

IdHistory     IdProject     IdStatus

1                    1                1

2                    1                2

3                    1                3

4                    1                1

5                    1                2

6                    2                1

7                    2                2

8                    2                3

9                    3                1

10                  3                2

I need to show a table with the Name of the Projects who have the last status (bigger IdHistory) = 2 (paused)

With the sample data the table should show

Project1

Project3

Best Regards

P.S. I've found some answers in the forum but they all use some "Group by" and "Max" at the data loading phase but i need to do this in a expression.

[sunny_talwar]

The output required is just the name of the projects? You do not want to see the status? I am sort of confused

[#3]

I need to show only the name of projects whose last status = 2 so i already know what the status is

[sunny_talwar]

Project 2 also seems to have latest status = 2, why is that one excluded from the output?

[#5]

That was a mistake i fixed it.

[maxgro · Accepted solution]

maybe with a calculated dimension

= aggr(if(FirstSortedValue(IdStatus, -IdHistory)=2, ProjectName),IdProject)

[#7]

WOW. That just work nicely.

I was trying to do something very similar but i was always getting an "Invalid dimension" message even when in the expression box everything seems ok.

My code was

= if(FirstSortedValue(Aggr(Max(IdHistory) ,IdProject), -IdHistory) = 2, ProjectName)

Very similar to yours but with the Aggr and FirstSortedValue changed.

Could you please tell me what i was doing wrong?