Aggregate Issue on using Whole Set ({1}) in Set Analysis

[erric3210]

Hi All,

I've facing issue with Aggr() function when using 1 in Set analysis. With $, it is working but when using whole set  then it is not giving me the correct result. I need your help on this issue. I've explained the issue below:

@sunny_talwar @marcus_sommer 

Raw Data Source:

Issue:

For reference, I've attached the sample file.

Regards,

Eric

[MayilVahanan · Accepted solution]

HI @erric3210 

Try like below

=Aggr(if(Rank(SUM({1<Category=P(Category)>} Units),1,1)<=1,Only({1}Name&'-'&Country)),Key)

[erric3210]

Hi  @MayilVahanan,

Thanks for the solution. But, why it's not working without Only() function. As my expression is returning one single row based on Rank 1. So, it should populate result based on that. But it's not working. Can you please explain me the logic behind that.

Regards,

Eric

[MayilVahanan]

Hi @erric3210 ,

Your selection is "Id:1" , so there is no country called UK. for that selection.

But based on ur requirement, you need to ignore the selection and display the values which has rank 1. 

So you are using {1} to ignore the selection in if statement, condition is satisfied , but in true part, if we just use "Name&'-'&Country", it doesn't has value (because its  'Jack-UK' is in exclude state). so need to ignore the selection in true statement also. that's the reason used only() function with {1} ignore selection .

hope u got it.

[erric3210]

Hi Mayil,

Thanks again for the explanation but that's the if part. I already ignored the selections and used 1 in the set expression Rank(SUM({1<Ca .........

Mayil, if you see in Chart 2 values are coming there. Ho w come they are coming. Need to know that.

Regards,

Eric