topic Re: aggr in Straight Table not giving consistent results in App Development

aggr in Straight Table not giving consistent results

tracysmart — Tue, 21 Dec 2021 21:48:24 GMT

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?

Re: aggr in Straight Table not giving consistent results

lironbaram — Wed, 19 Aug 2020 13:39:40 GMT

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)

Re: aggr in Straight Table not giving consistent results

tracysmart — Wed, 19 Aug 2020 14:26:17 GMT

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?

Re: aggr in Straight Table not giving consistent results

lironbaram — Wed, 19 Aug 2020 14:42:55 GMT

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)

Re: aggr in Straight Table not giving consistent results

tracysmart — Wed, 19 Aug 2020 14:59:43 GMT

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 🙂