
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Find max date using Firstsortedvalue & aggr
Hi,
Below is my sample data:
Transaction Date | Amount |
1/27/2021 | 112.90 |
1/26/2021 | 115.00 |
1/25/2021 | 115.50 |
1/24/2021 | 115.50 |
1/23/2021 | 115.50 |
1/22/2021 | 115.50 |
1/21/2021 | 115.70 |
1/20/2021 | 115.90 |
1/19/2021 | 115.30 |
1/18/2021 | 114.70 |
1/17/2021 | 114.70 |
1/16/2021 | 114.70 |
1/15/2021 | 114.70 |
1/14/2021 | 115.30 |
1/13/2021 | 115.00 |
1/12/2021 | 115.00 |
1/11/2021 | 114.60 |
1/10/2021 | 114.80 |
1/9/2021 | 114.80 |
1/8/2021 | 114.80 |
1/7/2021 | 114.70 |
1/6/2021 | 113.90 |
1/5/2021 | 112.70 |
1/4/2021 | 111.90 |
1/3/2021 | 111.90 |
1/2/2021 | 111.90 |
1/1/2021 | 111.90 |
I need to find date as 1/4/2021 when amount is 111.90
my set analysis is below: but issue is date is picking random like sometimes its coming 1/1/2021 & sometimes its coming 1/4/2021. But i need always should consider max date (1/4/2021) for that amount group
FirstSortedValue(distinct TransactionDate,aggr(Sum({<[TransactionDate]={">=$(=Yearstart(Max([TransactionDate]))) <=$(=date(Max([TransactionDate]),'MM/DD/YYYY'))"}>}[Amount])
,TransactionDate))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Perhaps this?
FirstSortedValue(distinct TransactionDate, aggr(Sum({<[TransactionDate]={"$(=date(Max([TransactionDate]),'MM/DD/YYYY'))"}>}[Amount]) ,TransactionDate))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Anil,
actually the logic is like: if you see the data in above table, for current YTD range, first deriving min amount based on transaction date, so here 111.90 is lowest amount which is coming for 4 date i.e. 1st to 4th Jan 2021. So among these 4 date i need to take latest date that should be 4th Jan 2021.
Your expression is giving only the max date irrespective of amount.
I have to first hit the lowest amount then need the latest date for that amount. my given set analysis is working if am amount is coming only for one date. But if lowest amount is coming for multiple dates then latest date is not coming. It picks up a random date from 1st to 4th jan 2021.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
If that is the case, This may work?
FirstSortedValue(distinct TransactionDate, Max({<[TransactionDate]={"$(=date(Max([TransactionDate]),'MM/DD/YYYY'))"}>} aggr(Sum({<[TransactionDate]={">=$(=Yearstart(Max([TransactionDate]))) <=$(=date(Max([TransactionDate]),'MM/DD/YYYY'))"}>}[Amount])
,TransactionDate)))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This is giving an error: Nested aggregation not allowed

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
can we set rank in first sorted value function? If yes how to set nth rank here it is 4.
If you give 4 manually its picking 4th jan. or any other way?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
For your 2 questions, I have given answers for both.
FirstSortedValue(distinct TransactionDate, Aggr(Max({<[TransactionDate]={"$(=date(Max([TransactionDate]),'MM/DD/YYYY'))"}>} aggr(Sum({<[TransactionDate]={">=$(=Yearstart(Max([TransactionDate]))) <=$(=date(Max([TransactionDate]),'MM/DD/YYYY'))"}>}[Amount])
,TransactionDate)),TransactioDate))
For second once, you can try this way
FirstSortedValue(distinct TransactionDate,aggr(Sum({<[TransactionDate]={">=$(=Yearstart(Max([TransactionDate]))) <=$(=date(Max([TransactionDate]),'MM/DD/YYYY'))"}>}[Amount])
,TransactionDate), 4)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
hi Anil,
For the first set expression, its giving max transaction date i.e. 27th jan which overall max transaction date. But i need 4th jan for the group where amount is 111.90.
2nd expression i tried earlier. But i cant hardcode 4 like this, data can be changed and can be 3 also. so how to show rank argument as generic.
Thanks
Deblina
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
PFA

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
HI Anil,
your expression logically should give 01/04/2021.
but for me its not coming.
Given the snapshot.
