topic issue with set analysis in QlikView

issue with set analysis

Thu, 22 Jul 2010 15:38:09 GMT

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

issue with set analysis

syed_muzammil — Thu, 22 Jul 2010 16:19:46 GMT

Hi Jean,

sum (total<Country> Population)

issue with set analysis

Thu, 22 Jul 2010 17:25:53 GMT

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

Thu, 22 Jul 2010 17:52:23 GMT

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

Thu, 22 Jul 2010 17:59:15 GMT

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

Thu, 22 Jul 2010 18:10:32 GMT

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

Thu, 22 Jul 2010 19:47:35 GMT

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

issue with set analysis

Thu, 22 Jul 2010 20:03:06 GMT

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

Thu, 22 Jul 2010 20:14:11 GMT

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

where you bring an answer.

JJJ

issue with set analysis

Thu, 22 Jul 2010 20:46:27 GMT

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!

issue with set analysis

Anonymous — Fri, 23 Jul 2010 03:55:36 GMT

Check out the "Set Analysis" section in the QlikView F1-help. Definitely my favorite source for troubleshooting my botched set analysis syntax when I run into trouble.