topic Re: Calculate Aggregates in App Development

Calculate Aggregates

mateo_florence — Tue, 21 Dec 2021 22:04:53 GMT

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

Re: Calculate Aggregates

Gysbert_Wassenaar — Wed, 23 Jan 2019 15:30:58 GMT

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

Re: Calculate Aggregates

agigliotti — Wed, 23 Jan 2019 16:02:27 GMT

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

Re: Calculate Aggregates

mateo_florence — Thu, 24 Jan 2019 08:59:20 GMT

Hello Agigliotti!!

Thank you for your answer!

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

Re: Calculate Aggregates

agigliotti — Thu, 24 Jan 2019 09:09:19 GMT

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