Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
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] ) )
Use ID ATM as dimension and use max(Date) and firstsortedvalue(Amount, -Date) as expressions.
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] ) )
Hello Agigliotti!!
Thank you for your answer!
I have used your expression in KPI and it is what i need!!