QlikView 12 AGGR SUM to LAST VALUE, not FIRST

[#1]

I am trying to take the SUM of a given Delay ID Delay amount which has multiple updates and attach that to the most recent update line.  However, the SUM is being displayed on the first line loaded from Excel, which in this case is when the issue was first recorded.  I'm looking to automate this and not have to make sure the most recent updates come first in Excel.  I have two expressions on this table in Qlik.

1-=FirstSortedValue(UpdateID,DelayID)

2-=aggr(Sum(DelayAmtDays),DelayID)

Sample straight table from QLIK:

DelayID	Update Date	Update #(this is really the updateID)	Project	Delay (Days)	Reason	Total Issue Delay
1	2/6/2017	2	test1	21	updated 2	-
1		1	test1	21	intial issue	42

My preferred output:

DelayID	Update Date	Update #	Project	Delay (Days)	Reason	Total Issue Delay
1	2/6/2017	2	test1	21	updated 2	42
1		1	test1	21	intial issue	-

How can I get the sum of DelayID=1 delay amount to appear on the latest update line, and NOT the first value loaded from Excel?

[sunny_talwar]

May be this:

If(RowNo() = NoOfRow(), Sum(TOTAL <DelayID> DelayAmtDays))

[#3]

Thank you for the quick response, but this shows the sum for all updates.  Both update 1 and 2 have a total issue delay of 42.

[swuehl · Accepted solution]

Maybe like

=If( Only(updateID) = Max(TOTAL<DelayID> updateID), Sum(TOTAL<DelayID> DelayAmtDays) )

[#5]

Excellent Stefan.  I will mark your answer as correct! Just for my understanding...This expression is taking the only update ID equal to the max update ID of a given set of delay IDs.  When the max update ID is found, sum.  Is that corrrect? THANK YOU!!

[swuehl]

updateID and DelayID should be dimensions in your chart, right?

Then Only(updateID) will just return the current dimensional value and Max(TOTAL<DelayID> updateID) the max value within the DelayID group. Just create two additional expressions to check.

(Note: Only() is just use to make the aggregation explicite)

If the current dimensional value of updateID equals the max value within DelayID group, then Sum DelayAmtDays across the group.

[#7]

This is for a straight table, so several dimensions.  By making aggregation explicit, do you mean restricting the aggregation to just one update ID right?

[swuehl]

Use Aggregation Functions!