Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

How to get the last value per group

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.

1 Solution

Accepted Solutions
MVP
MVP

Re: How to get the last value per group

maybe with a calculated dimension

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

6 Replies

Re: How to get the last value per group

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

Not applicable

Re: How to get the last value per group

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

Re: How to get the last value per group

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

Not applicable

Re: How to get the last value per group

That was a mistake i fixed it.

MVP
MVP

Re: How to get the last value per group

maybe with a calculated dimension

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

Not applicable

Re: How to get the last value per group

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?

Community Browser