Find max date using Firstsortedvalue & aggr

[deblina_rai]

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

[Anil_Babu_Samineni]

Perhaps this?

FirstSortedValue(distinct TransactionDate, aggr(Sum({<[TransactionDate]={"$(=date(Max([TransactionDate]),'MM/DD/YYYY'))"}>}[Amount]) ,TransactionDate))

[deblina_rai]

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.

[Anil_Babu_Samineni]

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

[deblina_rai]

This is giving an error: Nested aggregation not allowed

[deblina_rai]

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?

[Anil_Babu_Samineni]

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)

[deblina_rai]

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

[Anil_Babu_Samineni]

PFA 

[deblina_rai]

HI Anil,

your expression logically should give 01/04/2021.

but for me its not coming.

 Given the snapshot.