issue with set analysis

[#1]

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

Thanks in advance

JJJ

[syed_muzammil]

Hi Jean,

sum (total<Country> Population)

[#3]

Hi

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

I think we need set analysis.

thanks anyway

JJJ

[#4]

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.

[#5]

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

[#6]

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}).

[#7]

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 ?

thanks for your help

JJJ

[#8]

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.

[#9]

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

where you bring an answer.

JJJ

[#10]

Nice, I never did see the additions to that post. I think the new functions are a lot better than the old way. I don't know why QlikView made them so hard to find.

EDIT: I just implemented this on an app I'm working on and it is a ton faster than Concat. With Concat, I had to set calculation conditions to prevent charts from loading with too many records. Using P() instead I was able to load 10 times as many records faster than Concat. No more watching chart rendering progress bars!