Get Transaction Date for Largest Amount

[wwconslt]

Hello,

I'm trying to create a list report where each line is a person with their highest transaction amount and the date of the highest transaction amount.  People have multiple transactions over several years.

I created a custom data item (calc_LargestAmt) that successfully returns the highest transaction amount:
maximum([AMT] for [ID])

But the custom data item (calc_LargestAmtDate) I created to get the corresponding date isn't working.  It's returning the most recent transaction date instead of the date of the highest transaction:

maximum([TRANS_DATE] for [calc_LargestAmt], [ID])

I tried attaching a screen shot but got an error message (Cannot access attachments upload path!)

Any help is greatly appreciated.

[bus_pass_man]

It's not an answer to your question but you're not alone with that Cannot access attachments upload path error.  I'm happy that it's not just me.

https://www.goodreads.com/quotes/255918-shared-pain-is-lessened-shared-joy-is-increased-thus-we

[dougp]

I'm guessing you query to get results like.

<query1>
PersonId
PersonName
TransactionDate
TransactionAmount

There will be a couple ways to use that to get the results you need.  The first is the simplest to understand:

Summarize query1:
<query2>
PersonId
PersonName
Max(TransactionAmount) as TransactionAmount

Join query1 and query2 on PersonId and TransactionAmount to get:
<query3>
PersonId
PersonName
TransactionDate
TransactionAmount
(Basically, use query2 to filter query1 to only show the row that has the maximum TransactionAmount for each PersonId.)


The other way would involve doing it all in one query using windowing functions in filters.  While these may perform faster, they can be more difficult to conceptualize and may not work in all cases.
<query1 filter>
TransactionAmount = maximum(TransactionAmount for PersonId)

[MFGF]

It's not an answer to your question but you're not alone with that Cannot access attachments upload path error.  I'm happy that it's not just me.

https://www.goodreads.com/quotes/255918-shared-pain-is-lessened-shared-joy-is-increased-thus-we

For things like images in your post, it's easiest to upload the image to a site like imgbb and post the BBCode for the image in your Cognoise post.

Cheers!

MF.

[srmoure]

I would use the Rank function to obtain rank 1 the record with the highest transition value for each employee. Then filter the records ranked 1

Rank ( [Amount]  for EmployeeID)