Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

QlikView 12 AGGR SUM to LAST VALUE, not FIRST

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:

DelayIDUpdate DateUpdate #(this is really the updateID)ProjectDelay (Days)ReasonTotal Issue Delay

1

2/6/20172test121updated 2-
11test121intial issue42

My preferred output:

DelayIDUpdate DateUpdate #ProjectDelay (Days)ReasonTotal Issue Delay

1

2/6/20172test121updated 242
11test121intial 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?

1 Solution

Accepted Solutions
MVP
MVP

Maybe like

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

View solution in original post

7 Replies

May be this:

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

Not applicable

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.

MVP
MVP

Maybe like

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

View solution in original post

Not applicable

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

MVP
MVP

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.

Not applicable

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?

MVP
MVP