KPI wrong result with Min() function because of different group by level

[sdavinblanc]

Hello

I have to calculate the lowest point in a data set for various metrics between 2002 and today
I have three filter panel: DESTINATION, AREA and DEPARTMENT.
Let's call MEASURE_A one of the metrics I have to calculate.
When the user selects a DESTINATION, an AREA and a DEPARTMENT, the calculation is performed correctly.

The definition of my variable that I call in my KPI : =Min({<Annee_Mois={">$(vStartDate) < $(vAnneeMois)"}, [# MESURE_A]-={0}>} [# MESURE_A])
(vStartDate = 200201, vAnneeMois=202507 - I also remove the values ​​at 0 for the calculation of the minimum)

However, if the user selects a DESTINATION and an AREA the calculation no longer works because it continues to search for the lowest point in the data at the level of the different DEPARTMENT linked to the AREA region instead of searching the total of all DEPARTMENT in the selected AREA. The same problem occurs if the user selects a specific DESTINATION without selecting an AREA or a DEPARTMENT.

From a database perspective, I understand that the GROUP BY method based on the user's selections is not the same; however, I don't see how to define my variable for all scenarios (selection of a DESTINATION only, AREA only, or a DEPARTMENT only).

One solution for me would be to test which filter panel are selected and according to the selection call a different variable that would consider the necessary grouping.

Is there a simpler solution using set analysis?

Thank you in advance for your help.

[FedericoDellAcqua]

Are your variables $(vStartDate), $(vAnneeMois) defined in front-end? In that case they will be reloaded after the field selections, creating problems in your KPI

[sdavinblanc]

The variable are loaded in script but I don't think the problem is due to variables vStartDate and vAnneeMois.

I think the problem come from my MEASURE_A's definition which is always considering the group by : DESTINATION, AREA and DEPARTMENT even if no DEPARTMENT are selected by user.

Ex : my user is selecting DESTINATION = 'Bureau' and AREA='44-Grand Est' with NO DEPARTMENT selected

> In that case the MIN() function should return the min(measure_A) from a group by DESTINATION and AREA only but it's not the case it still looking by DEPARTMENT too.

And i don't know how to set up it in order the group by change depending which panel filter are selected.

Hope it's clear ! Sorry for my english

[sdavinblanc · Accepted solution]

Hi,

Finaly the right formula is : 

MIN( AGGR(SUM({<Annee_Mois={">$(vStartDate) < $(vAnneeMois)"}, [# MESURE_A]-={0}>} [#MESURE_A]),YEAR_MONTH))

It works correctly now