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

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

qlikuser222 — Sat, 16 Nov 2024 04:14:43 GMT

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 — Thu, 07 Mar 2019 20:27:50 GMT

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

FirstSortedValue(Amount, -Date)

-Rob

qlikuser222 — Thu, 07 Mar 2019 21:07:09 GMT

Thanks for the reply,

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

rwunderlich — Fri, 08 Mar 2019 02:17:09 GMT

In that case try:

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

sunny_talwar — Fri, 08 Mar 2019 02:19:44 GMT

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