Wrong KPI expression

[sdavinblanc]

Hello

I have a KPI that is searching for the MIN value of a measure_A between 2008 and now (Year_Month format) using the following expression :
=Min(Aggr(Sum({<Year_Month={">=$(vStartDate) <=$(vEndDate)"}, [#mesure_A ]-={0}>}[#mesure_A ]), Year_Month ))

On my visualization, the user has the option to filter by DESTINATION, AREA, and DEPARTMENT.

My expression works correctly.

The user wants me to display the year_month corresponding to this MIN value. On the screenshot, it's B circled in green.
If the user selected a DESTINATION, an AREA and a DEPARTMENT, the result is correct because I am at the lowest level of my grouping.

However, if the user doesn't select a department, my result is no longer correct. I know it's the expression to find this year_month where my measure is the MIN that isn't working, but I can't find the correct way to write it.

My formula B to find the year_month:

ONLY({<[#Mesure_A]={$(vPointBas2002_Autorisé_12M)}>}YEAR_MONTH_NAME)

My variable "vPointBas2002_Autorisé_12M" is the result of the expression I use to calculate my lowest point: : =Min(Aggr(Sum({<Year_Month ={">=$(vStartDate) <=$(vEndDate )"}, [#mesure_A ]-={0}>}[#mesure_A ]), Year_Month )) .

I would have to replace in my formula B the element [#Measure_A] with the following aggregation formula :

Aggr(Sum({<Year_Month ={">=$(vStartDate) <=$(vEndDate)"}, [#mesure_A ]-={0}>}[#mesure_A ]), Year_Month )

But i can't find how to write it correctly. I have "error in set modifier expression !

Can you help me with the right way to write it ?

Thanks

[Lucke_Hallbergson · Accepted solution]

Try this

FirstSortedValue(YEAR_MONTH_NAME, Aggr(Sum({<Year_Month={">=$(vStartDate) <=$(vEndDate)"}, [#mesure_A]-={0}>}[#mesure_A]), Year_Month))

[sdavinblanc]

Hi @Lucke_Hallbergson 

It works perfectly, thank you very much ! Simpler than I thought

Very helpful thank you again

[marcus_sommer]

Your expression of:

Min(Aggr(Sum({<Year_Month={">=$(vStartDate) <=$(vEndDate)"}, [#mesure_A ]-={0}>}[#mesure_A ]), Year_Month ))

returned a single value per Year_Month. Should there more dimensions be considered they need to be listed in the aggr() too, like:

Min(Aggr(Sum({<Year_Month={">=$(vStartDate) <=$(vEndDate)"}, [#mesure_A ]-={0}>}[#mesure_A ]), Year_Month, DESTINATION, AREA, DEPARTMENT))

Further be aware that depending on the data-set, data-model and object-structure all aggregation-parts might become the appropriate set statement, like:

min({ Set } aggr({ Set } sum({ Set } Field), Dim1, Dim2))

Your syntax-error could have various reasons especially using a variable. At first the logic should be working with direct code and then any parts might be outsourced in variables.

An aggr() itself is already a complex logic and nesting them within further aggregations could become really complicated. Therefore I suggest to consider simpler methods maybe by adding appropriate granular levels within a table and/or not using a single expression else n ones, like:

MyExpression * MyCondition1 * MyCondition2

which means the main-expression could be kept more simple and returning n results and the 0/1 conditions are used to filter the n ones to the finally wanted ones. Even if this didn't fulfill the entire requirement such approach is helpful to develop the needed logic (detecting syntax/logically errors within a complex approach is much more harder as splitting it in smaller parts and combining the working ones later).