Set Analysis

[joaovieira]

Hello community, i am trying to create a KPI that returns the country of the customer that has the more sales, to get the customer i used this expression:

concat(aggr(if(rank(sum({$<Year={'$(vMaxYear)'},OrderDate={'<=$(vMaxDate)'}>}SalesFinal))<2,Customer),Customer),',')

Now the idea is to select the country of the person that the expression returns, any ideas?

Thank you for the help

[Anonymous]

If your expression works then you should just be able to change the "Customer" field to "Country" field as the data is linked so you will get the same result but it will return the Country

[Mark_Little · Accepted solution]

HI

If your above set analysis is working. i would try putting it in a Variable. vCustomer

Then trying ONLY({<Customer={'$(vCustomer)'}>}Country).

Mark

[agigliotti]

maybe this:

=Only( Aggr( if( sum({$< Year = {'$(vMaxYear)'}, OrderDate = {'<=$(vMaxDate)'} >} SalesFinal) =

Max( Aggr( sum({$< Year = {'$(vMaxYear)'}, OrderDate = {'<=$(vMaxDate)'} >} SalesFinal), Country ) ), Country, null() ), Country ) )

[joaovieira]

Thank you, it works well and it will help with other things i wasn't able to do.