10 Replies Latest reply: Jul 22, 2010 11:55 PM by Johannes Sunden

# issue with set analysis

Hi

I have a table with 3 fields :

- city

- country (a city belongs to a unique country !)

- population

I want the user to choose, in a list box, a city then to display in a box the overall population of the country.

So, for 2 cities from the same country, I want to have the same results.

I suspect to use set analysis but i'm not so confortable with it

JJJ

• ###### issue with set analysis

Hi Jean,

sum (total<Country> Population)

• ###### issue with set analysis

Hi

No it doesn't work, just giving me the population of my selected city.

I think we need set analysis.

thanks anyway

JJJ

• ###### issue with set analysis

The formula is correct for getting the country population on each city record, but you probably need to ignore the selection along with it. Something like:

`sum ({1}total<Country> Population)`

But then I don't know why you would need a City selection.

• ###### issue with set analysis

Thanks Nat,

Of course my formula is more complex.

For a selected city, i want to calculate a ratio

sum(Population) of the selected city / sum of the population for all the cities belongs to the country of the selected city.

So I need the way to calculate the denominator of the ratio

regards

JJJ

• ###### issue with set analysis

Ok, that makes sense. Your chart will only have the selected city in it? Then use:

`Sum(Population)/Sum({1} TOTAL <Country> Population)`

The numerator will be the Sum of population respecting dimensions and selections, so it will be just your selected city. The denominator will be the Sum of population ignoring dimensions, except country (TOTAL <Country>) and ignoring selection ({1}).

• ###### issue with set analysis

Nat,

Your formula is OK in straight table but not in a text box.

I found out this :

Sum(Pop)/Sum({1 <Country=P(Country)>} Pop)

It works well, but I dont undestrand P(). Maybe you have an idea ?

JJJ

• ###### issue with set analysis

I have never seen the P() function before and it seems to be absent from the Reference Manual. I did find reference to it on the QlikView site. Look here: http://community.qlik.com/media/p/72390.aspx

It seems to do the same thing as Concat(), which is list the selected (or maybe possible if it's working for you when a City is selected). So, I think your formula is saying ignore selections ({1}), but make Country equal to the possible countries.

That link also says there is an E() function, which seems to give everything except the selected items. I've found myself in need of a similar formula to the one you have, but I could not figure out how to do it in Set Analysis. I'm going to do some testing on it, but it seems promising.

Maybe that's what the P stands for Possible and E is Excluded?

EDIT: Found it in the Reference Guide. Page 360 of Book III in the Set Analysis section: Set Modifiers with Implicit Field Value Definitions.

• ###### issue with set analysis

Thanks very much, I also found an intersting post http://community.qlik.com/forums/t/17500.aspx