Aggr Function

[hanna_choi]

Hello.

I'm Testing with Qlik Sense June 20018.

When I used Aggr function, I was found problem.

Why the Aggr function return wrong results?

What do I do for right result?

[Gysbert_Wassenaar]

Why do you think the result is wrong? Because you have 21 countries and the count(aggr... returns 22? Check for sales that don't have a country.

[hanna_choi]

Hello  Gysbert Wassenaar

The Aggr means the copunt of country that has the sum of sales. Is it my misunderstand? I think Even though some country would have no sales,  but the sales have to include the country.

So I thought the result is wrong.

I checkd the count of sales. I don't know why it is 22.

[nsetty]

Try using Distinct keyword

and also dumping Aggr contents onto text box.

=concat(aggr(sum(sales), Country), '|')

[Gysbert_Wassenaar]

If you want a count of countries that have sales you should use this:

count({<Country={"=sum(Sales)>0"}>}distinct Country)

[dx_anupam]

Hi Hanna,

It seems to be you have some null values in country column and which is causing discrepancy in both count.

Please uncheck the tick mark next to "suppress when value is null" under dimension for straight table.

Regards,

Anupam 

[hanna_choi]

Thanks you everyones.

It is mystery.

After = count({<Country={"=sum(Sales)>0"}>}distinct Country),
I got the 21.

THe country is not include NULL values. I unchecked include null value option

[nsetty · Accepted solution]

Hi Hanna,

Can you try dumping AGGR virtual table entries onto textbox object and verify why you were getting 22 records.

=concat(aggr(sum(sales), Country), '|')

Also, mark helpful/answered if you agree with a solution

Thanks

Nagesh

[hanna_choi]

HI Nagesh Setty

Thank you for your anwser.

I found the 0(zero)

But I don't know why 0 is made and what country include 0.

So I just would understand Aggr function include the country that don't make the sales.