topic Re: Set Analysis in QlikView

Set Analysis

Wed, 29 May 2013 11:20:03 GMT

Hi,

I am practising with the Set Analysis expresion and I have a doubt.

I would like to know if it is possible calculate dinamically the value for a field from another field in the same register.

For example:

Country	Continent	Exports	AVG(Exports)
Spain	Europe	150	175
France	Europe	200	175
Brazil	America	500	300
Argentina	America	150	300
USA	America	250	300

I would like to calculate dinamically the average exports for continent in order to color those conutries that have a value higher than the average.

I have got this expression:

average_exports:=Avg({1<Continent={$(=Continent)}>} Total Exports)

This expression only works when I select a Continent (thanks to the symbol '$') but I would like to get the value for all the fields without selecting any Continent.

I think that a function like "Current(Continent)" or "this(Continent)" would be usefull but I haven't found it.

Thanks in advance,

Mario

Re: Set Analysis

jonathandienst — Wed, 29 May 2013 11:48:51 GMT

Assuming you are putting this in a table with country and continent as dimensions, then try the following:

Country Average = Avg(Exports)

Content Average = Avg(TOTAL <Contintent> Exports)

Hope that helps

Jonathan

Oops, Juan is right. I have corrected the expression

Re: Set Analysis

Wed, 29 May 2013 12:34:09 GMT

As Jonathan says, I think this is the best approach. But using Continent instead of Country. I have attached a document with the solution.

Re: Set Analysis

Wed, 29 May 2013 13:43:21 GMT

Thank you very much for your answer Juan Gerardo.

Your idea works perfectly.

I would like to point out that the final expresions are:

Exports = sum(Exports)

Average_Exports = Avg (TOTAL<Continent> Exports)

The value "Exports" used in the "Average_Exports" expresion is not the "Exports" expresion, is the "Exports" column. In order to clarify the example I will rewrite it:

Export_Country = sum(Exports)

Average_Export_Country=Avg(TOTAL<Continent>Exports)