Aggregate sum values by ID and max available date per ID

[therealdees]

Hi,

I'm trying to sum values for different store ID that might have a different max date for each.

For instance, I could have 10 stores with a max date on 13/02/2024 but another store where the max date is 10/02/2024. In this case I must sum every value for these 10 stores (considering the 13/02) plus the special store where the date is 10/02.

I was using the following expression and it was working until we had a store that didn't open these days:

Sum({<key_calendario = {"$(=Floor(Max(projecao_faturamento_data)))"}>} projecao_faturamento_dia)

I tried to tweak around using aggr, but without sucess. Does anyone have a clue on how to achieve this?

PS: the value is gonna be shown in a KPI, not a table, that why it must be aggregated to work (I think)

[therealdees · Accepted solution]

Ok, got it with FirstSortedValue.

Sum(Aggr(FirstSortedValue(projecao_faturamento_dia, -projecao_faturamento_data), key_cnpj))

With the help of this thread:

https://community.qlik.com/t5/QlikView-App-Dev/Help-with-Max-and-AGGR-Function/td-p/1820575