Calculate Aggregates

[mateo_florence]

Hi!

I need to calculate a KPI, I put an example to explain it:

Date	ID ATM	Amount
14/01/2019	22	1.000,00
14/01/2019	99	5.000,00
10/01/2019	22	3.000,00
17/12/2018	49	10.000,00
22/11/2018	49	8.000,00
29/10/2018	49	3.000,00

I need to SUM the Amount of the Max Date of each ATM, so in this case i have to sum the 'blue' values.

Then my KPI is: 16.000,00

Date	ID ATM	Amount
14/01/2019	22	1.000,00
14/01/2019	99	5.000,00
10/01/2019	22	3.000,00
17/12/2018	49	10.000,00
22/11/2018	49	8.000,00
29/10/2018	49	3.000,00

Any Suggestions? Can i do it using set analysis??

Thanks!!

[Gysbert_Wassenaar]

Use ID ATM as dimension and use max(Date) and firstsortedvalue(Amount, -Date) as expressions.

[agigliotti · Accepted solution]

i suggest you working on the script as below:
temp_table:
load
[ID ATM],
max(Date) as [Date Max]
Resident your_table group by [ID ATM];

Left Join (your_table)
load * Resident temp_table;

DROP Table temp_table;

finally in UI use the below expression for your KPI:
=sum( if( Date = [Date Max], Amount ) )

OTHERWISE with no modification on the script, use the below expression in KPI:

=sum( aggr( FirstSortedValue( Amount, -Date ), [ID ATM] ) )

[mateo_florence]

Hello Agigliotti!!

Thank you for your answer!

I have used your expression in KPI and it is what i need!!

[agigliotti]

ok great!
please mark the anwser as correct to help others.