aggr in Straight Table not giving consistent results

[tracysmart]

I am struggling to see why my Aggr column is not giving consistent results.

I have the following fields

IncidentReference,

Claim_Date_Transaction_Created,

Claim_Trans_Authorisation_Status, 

Claim_Net

I want to return the Claim_Net value where the Claim_Date_Transaction_Created is the maximum (per Incident reference based on certain Status)

I have used this expression, which works for the most part. 

if(Date(floor(Claim_Date_Transaction_Created),'DD/MM/YYYY')=date(floor(Aggr(Max({<Claim_Trans_Authorisation_Status={'Approved', 'Approved Direct Payment'}>}Claim_Date_Transaction_Created),IncidentReference)),'DD/MM/YYYY')
,Claim_Net,0)

I have also tried using just an aggr expression without the IF statement but again, cant seem to get the expression quite right

The attached spreadsheet shows the resulting straight table, but in some cases I get a zero (highlighted in red) and I cant understand why this might be?

[lironbaram]

hi 

you can try this expression 

if(Claim_Date_Transaction_Created= max({<Claim_Trans_Authorisation_Status={'Approved', 'Approved Direct Payment'}>}total <IncidentReference> Claim_Date_Transaction_Created),Claim_Net,0)

[tracysmart]

Hi

thank you for this, this works perfectly.

Using your solution, I am trying to add to a table that has just one row per IncidentReference, so no individual dates or values are in this. I cant use the if statement as that wont evaluate. I have converted to set analysis below;

This works when I have an IncidentReference selected but not when I dont?

sum({<Claim_Date_Transaction_Created={"$(=Date(Max({<Claim_Trans_Authorisation_Status={'Approved', 'Approved Direct Payment'}>}total <IncidentReference> Claim_Date_Transaction_Created),'DD/MM/YYYY'))"}>}Claim_Net)

I must be missing something else? 

[lironbaram · Accepted solution]

if 

that case  assuming your table has incident reference as only dimension 
use this expression :

FirstSortedValue({<Claim_Trans_Authorisation_Status={'Approved', 'Approved Direct Payment'}>}Claim_Net,-Claim_Date_Transaction_Created)

[tracysmart]

Of course, FirstSortedValue! How did I forget that.

I was so wrapped up thinking I needed an aggr that I forgot about functions like these.

Thank you 🙂