Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikuser222
Creator
Creator

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

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.

Labels (1)
1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

4 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

FirstSortedValue(Amount, -Date)

-Rob

qlikuser222
Creator
Creator
Author

Thanks for the reply,

 

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

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