Aggregate sum values by ID and max available date per ID

pedrohenriqueperna — Wed, 14 Feb 2024 15:06:55 GMT

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)

Re: Aggregate sum values by ID and max available date per ID

pedrohenriqueperna — Wed, 14 Feb 2024 15:23:37 GMT

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

topic Aggregate sum values by ID and max available date per ID in App Development

Aggregate sum values by ID and max available date per ID

Re: Aggregate sum values by ID and max available date per ID