Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
erric3210
Contributor III
Contributor III

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

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:

Aggr_Source Table.png

Issue:

Issue_Aggr.png

 

For reference, I've attached the sample file.

Regards,

Eric

1 Solution

Accepted Solutions
MayilVahanan

HI @erric3210 

Try like below

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

MayilVahanan_0-1606787139219.png

 

Thanks & Regards,
Mayil Vahanan R

View solution in original post

4 Replies
MayilVahanan

HI @erric3210 

Try like below

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

MayilVahanan_0-1606787139219.png

 

Thanks & Regards,
Mayil Vahanan R

View solution in original post

erric3210
Contributor III
Contributor III
Author

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.

Thanks & Regards,
Mayil Vahanan R
erric3210
Contributor III
Contributor III
Author

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