Aggr Sum not working with Multiple values

[#1]

Hi all,

I create a variable contains the following formula:

aggr(sum(sales),country)

And I created a straight table showing <country> and <sales per country> by using the above variable. It works fine when I only choose 1 country. But when I select multiple countries in my report, value of this value becomes '-' for all the selected countries in the straight table.

Can someone give me a hand on this? Thanks.

[#2]

Hi

Why do you use aggr if you have <country> in your straigt table?
sum(sales) should work just fine.

/Fredrik

[ssamuels]

Hi,

Try using the NODISTINCT option when using the AGGR function:

AGGR(NODISTINCT sum(sales), country)

With the use of the NODISTINCT option the AGGR function will return multiple values.

---
Steven

[#4]

Hi Fredrik,

Actually, there are some other dimensions on my straight table and I need to set the background color according to the sum of sales by country.

I'm new to Qlikview, and I feel unsave to use sum(sales) instead of aggr because I don't know which level (dimension) Qlikview is grouping the data by. Is there any way I can ensure the sum is always by country level if I use sum(sales)? Thanks.

Regards,
Lewis

[#5]

Hi Steven,

I tried AGGR(NODISTINCT sum(sales), country), but it's not working. Actually, all the rows in my striaght table are already grouped by country and they should be distinct values already. Is there anything else I should check? Thanks.

Regards,
Lewis

[#6]

Hi Lewis

Any chance you can post an exemple application. That would probably give you a solution quickly 😉

/Fredrik

[#7]

Hi Fredrik,

Please find the attached file for a simplied version of my report. In this report, if you pick only 1 "TR", the value of variable - "V1" would be shown properly. However, if you select multiple values for "TR", the value of "V1" shown as "-" for all the selected rows in the straight table. Please let me know if you have any idea on how to fix this problem. Thanks a lot for your help.

Regards,
Lewis

[#8 · Accepted solution]

Hi

See the attached file. Minor changes but oh so important...

regards
/Fredrik

[#9]

Hi Fredrik,

It is exactly what I need, thanks a lot for your help.

Regards,

Lewis

[Nitish91]

Thanks! This saved me.