topic Re: QlikView 12 AGGR SUM to LAST VALUE, not FIRST in QlikView

QlikView 12 AGGR SUM to LAST VALUE, not FIRST

Wed, 08 Feb 2017 19:41:27 GMT

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?

Re: QlikView 12 AGGR SUM to LAST VALUE, not FIRST

sunny_talwar — Wed, 08 Feb 2017 19:50:34 GMT

May be this:

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

Re: QlikView 12 AGGR SUM to LAST VALUE, not FIRST

Wed, 08 Feb 2017 20:01:51 GMT

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.

Re: QlikView 12 AGGR SUM to LAST VALUE, not FIRST

swuehl — Wed, 08 Feb 2017 21:14:32 GMT

Maybe like

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

Re: QlikView 12 AGGR SUM to LAST VALUE, not FIRST

Wed, 08 Feb 2017 21:29:41 GMT

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!!

Re: QlikView 12 AGGR SUM to LAST VALUE, not FIRST

swuehl — Wed, 08 Feb 2017 21:45:52 GMT

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.

Re: QlikView 12 AGGR SUM to LAST VALUE, not FIRST

Thu, 09 Feb 2017 12:39:16 GMT

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?

Re: QlikView 12 AGGR SUM to LAST VALUE, not FIRST

swuehl — Thu, 09 Feb 2017 14:19:43 GMT

Use Aggregation Functions!