topic How to get the last value per group in App Development https://community.qlik.com/t5/App-Development/How-to-get-the-last-value-per-group/m-p/1239947#M24574 <HTML><HEAD></HEAD><BODY><P>Hi</P><P></P><P>First of all sorry for my bad english i hope you can understandme.</P><P></P><P>I'm loading 3 tables in qliksense</P><P></P><P><STRONG>Projects</STRONG></P><P></P><P><SPAN style="font-size: 13.3333px;">(Identity)</SPAN></P><P>IdProject&nbsp;&nbsp; ProjectName</P><P>1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Project1</P><P>2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Project2</P><P>...&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .....&nbsp;&nbsp; </P><P>N&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ProjectN</P><P></P><P><STRONG>Status</STRONG></P><P><SPAN style="font-size: 13.3333px;"><BR /></SPAN></P><P><SPAN style="font-size: 13.3333px;">(Identity)</SPAN></P><P>IdStatus&nbsp;&nbsp; StatusName</P><P>1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; OnTime</P><P>2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Paused</P><P>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Delayed</P><P>4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Other</P><P></P><P><STRONG>ProjectStatusHistory</STRONG></P><P></P><P><SPAN style="font-size: 13.3333px;">(Identity)</SPAN></P><P>IdHistory&nbsp;&nbsp;&nbsp;&nbsp; IdProject&nbsp;&nbsp;&nbsp;&nbsp; IdStatus</P><P>1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1</P><P>2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2</P><P><SPAN style="font-size: 13.3333px;">3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3</SPAN></P><P>4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1</P><P><SPAN style="font-size: 13.3333px;">5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2</SPAN></P><P><SPAN style="font-size: 13.3333px;">6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1</SPAN></P><P>7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2</P><P><SPAN style="font-size: 13.3333px;">8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3</SPAN></P><P>9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1</P><P>10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2</P><P></P><P>I need to show a table with the Name of the Projects who have the <STRONG>last status (bigger IdHistory)</STRONG> = 2 (paused)</P><P></P><P>With the sample data the table should show</P><P></P><P>Project1</P><P>Project3</P><P></P><P>Best Regards</P><P></P><P>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.</P></BODY></HTML> Tue, 21 Feb 2017 17:59:00 GMT 2017-02-21T17:59:00Z How to get the last value per group https://community.qlik.com/t5/App-Development/How-to-get-the-last-value-per-group/m-p/1239947#M24574 <HTML><HEAD></HEAD><BODY><P>Hi</P><P></P><P>First of all sorry for my bad english i hope you can understandme.</P><P></P><P>I'm loading 3 tables in qliksense</P><P></P><P><STRONG>Projects</STRONG></P><P></P><P><SPAN style="font-size: 13.3333px;">(Identity)</SPAN></P><P>IdProject&nbsp;&nbsp; ProjectName</P><P>1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Project1</P><P>2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Project2</P><P>...&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .....&nbsp;&nbsp; </P><P>N&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ProjectN</P><P></P><P><STRONG>Status</STRONG></P><P><SPAN style="font-size: 13.3333px;"><BR /></SPAN></P><P><SPAN style="font-size: 13.3333px;">(Identity)</SPAN></P><P>IdStatus&nbsp;&nbsp; StatusName</P><P>1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; OnTime</P><P>2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Paused</P><P>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Delayed</P><P>4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Other</P><P></P><P><STRONG>ProjectStatusHistory</STRONG></P><P></P><P><SPAN style="font-size: 13.3333px;">(Identity)</SPAN></P><P>IdHistory&nbsp;&nbsp;&nbsp;&nbsp; IdProject&nbsp;&nbsp;&nbsp;&nbsp; IdStatus</P><P>1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1</P><P>2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2</P><P><SPAN style="font-size: 13.3333px;">3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3</SPAN></P><P>4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1</P><P><SPAN style="font-size: 13.3333px;">5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2</SPAN></P><P><SPAN style="font-size: 13.3333px;">6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1</SPAN></P><P>7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2</P><P><SPAN style="font-size: 13.3333px;">8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3</SPAN></P><P>9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1</P><P>10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2</P><P></P><P>I need to show a table with the Name of the Projects who have the <STRONG>last status (bigger IdHistory)</STRONG> = 2 (paused)</P><P></P><P>With the sample data the table should show</P><P></P><P>Project1</P><P>Project3</P><P></P><P>Best Regards</P><P></P><P>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.</P></BODY></HTML> Tue, 21 Feb 2017 17:59:00 GMT https://community.qlik.com/t5/App-Development/How-to-get-the-last-value-per-group/m-p/1239947#M24574 2017-02-21T17:59:00Z Re: How to get the last value per group https://community.qlik.com/t5/App-Development/How-to-get-the-last-value-per-group/m-p/1239948#M24575 <HTML><HEAD></HEAD><BODY><P>The output required is just the name of the projects? You do not want to see the status? I am sort of confused</P></BODY></HTML> Tue, 21 Feb 2017 18:08:47 GMT https://community.qlik.com/t5/App-Development/How-to-get-the-last-value-per-group/m-p/1239948#M24575 sunny_talwar 2017-02-21T18:08:47Z Re: How to get the last value per group https://community.qlik.com/t5/App-Development/How-to-get-the-last-value-per-group/m-p/1239949#M24576 <HTML><HEAD></HEAD><BODY><P>I need to show only the name of projects whose last status = 2 so i already know what the status is</P></BODY></HTML> Tue, 21 Feb 2017 18:19:08 GMT https://community.qlik.com/t5/App-Development/How-to-get-the-last-value-per-group/m-p/1239949#M24576 2017-02-21T18:19:08Z Re: How to get the last value per group https://community.qlik.com/t5/App-Development/How-to-get-the-last-value-per-group/m-p/1239950#M24577 <HTML><HEAD></HEAD><BODY><P>Project 2 also seems to have latest status = 2, why is that one excluded from the output?</P></BODY></HTML> Tue, 21 Feb 2017 21:41:36 GMT https://community.qlik.com/t5/App-Development/How-to-get-the-last-value-per-group/m-p/1239950#M24577 sunny_talwar 2017-02-21T21:41:36Z Re: How to get the last value per group https://community.qlik.com/t5/App-Development/How-to-get-the-last-value-per-group/m-p/1239951#M24578 <HTML><HEAD></HEAD><BODY><P>That was a mistake i fixed it. <IMG src="https://community.qlik.com/legacyfs/online/emoticons/wink.png" /></P></BODY></HTML> Tue, 21 Feb 2017 22:48:50 GMT https://community.qlik.com/t5/App-Development/How-to-get-the-last-value-per-group/m-p/1239951#M24578 2017-02-21T22:48:50Z Re: How to get the last value per group https://community.qlik.com/t5/App-Development/How-to-get-the-last-value-per-group/m-p/1239952#M24579 <HTML><HEAD></HEAD><BODY><P>maybe with a calculated dimension</P><P></P><P>= aggr(if(FirstSortedValue(IdStatus, -IdHistory)=2, ProjectName),IdProject)</P></BODY></HTML> Tue, 21 Feb 2017 23:17:35 GMT https://community.qlik.com/t5/App-Development/How-to-get-the-last-value-per-group/m-p/1239952#M24579 maxgro 2017-02-21T23:17:35Z Re: How to get the last value per group https://community.qlik.com/t5/App-Development/How-to-get-the-last-value-per-group/m-p/1239953#M24580 <HTML><HEAD></HEAD><BODY><P>WOW. That just work nicely.</P><P></P><P>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.</P><P></P><P>My code was </P><P></P><P>= if(FirstSortedValue(Aggr(Max(IdHistory) ,IdProject), -IdHistory) = 2, ProjectName)</P><P></P><P>Very similar to yours but with the Aggr and <SPAN style="font-size: 13.3333px;">FirstSortedValue</SPAN> changed.</P><P></P><P>Could you please tell me what i was doing wrong?</P></BODY></HTML> Tue, 21 Feb 2017 23:51:15 GMT https://community.qlik.com/t5/App-Development/How-to-get-the-last-value-per-group/m-p/1239953#M24580 2017-02-21T23:51:15Z