Help With showing Max date for each row in starlight table?

[qlikuser222]

Hi Everyone,

I have a requirement where i need to show Amount tagged to each code with the respective Max date tagged to it.

I am unable to show the expected results and need help understanding the issue. I

PFA with example set table.

[rwunderlich]

Do you have only one Amount value for each Code/Date pair? If so then you can use:

FirstSortedValue(Amount, -Date)

-Rob

[qlikuser222]

Thanks for the reply,

Unfortunately no, Each Code/date may have multiple amount value.

[rwunderlich]

In that case try:

sum(If(Date = aggr(Nodistinct Max(Date), Code), Amount))

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[sunny_talwar · Accepted solution]

In that case try this

=FirstSortedValue(Aggr(Sum(Amount), Date, Code), -Aggr(Date, Date, Code))

or this

=Sum(Aggr(If(Max(TOTAL <Code> Date) = Date, Sum(Amount)), Code, Date))