topic Re: Aggregate Issue on using Whole Set ({1}) in Set Analysis in QlikView

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

erric3210 — Fri, 15 Nov 2024 23:32:31 GMT

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

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

MayilVahanan — Tue, 01 Dec 2020 01:45:45 GMT

HI @erric3210

Try like below

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

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

erric3210 — Tue, 01 Dec 2020 07:39:04 GMT

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

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

MayilVahanan — Tue, 01 Dec 2020 07:59:09 GMT

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.

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

erric3210 — Tue, 01 Dec 2020 08:06:23 GMT

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