# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
cancel
Showing results for
Did you mean:
Not applicable

## Complex calculated dimension

Hi

I have a single table with 3 dimensions (cities,  country, region)  and few indicators.

A city belongs to a country and a country belongs to a region.

An end user,  select a city  and I want to display a chart  which displays  3 sets of information for an indicator :

-  mean for the selected city

- mean for  the country where the city belongs

- mean for the region where the city belongs

I can create 3 expressions :

avg(  Value) ,   avg(    { 1  <COUNTRY= P(COUNTRY) > }       Value),   avg(    { 1  <REGION= P(REGION) > }       Value),

and it works well

But my objective is to  create a Dimension   which take account of my 3 sets and not to create expression.

I wonder if it's possible because the second set contains the first, and so on..

I'd like this because I want to add another dimension to display a stacked bar-chart

Thanks in advance for any idea

Regards

JJ

1 Solution

Accepted Solutions
Not applicable
Author

A colleague found out a smart solution using valuelist function :

Dimension :

 Valuelist(   'city'  ,  'country' , 'region')

Expression :

 =If(   Valuelist(   'city'  ,  'country' , 'region') = 'city' , avg(  Value) ,     If(   Valuelist(   'city'  ,  'country' , 'region') = 'country'    , avg(    { 1  }       Value),      If(   Valuelist(   'city'  ,  'country' , 'region') = 'region'  , avg(    { 1  }       Value) )))

Very useful for making comparaison !

4 Replies
Not applicable
Author

Any ideas or it's definitly not possible

JJ

Not applicable
Author

A colleague found out a smart solution using valuelist function :

Dimension :

 Valuelist(   'city'  ,  'country' , 'region')

Expression :

 =If(   Valuelist(   'city'  ,  'country' , 'region') = 'city' , avg(  Value) ,     If(   Valuelist(   'city'  ,  'country' , 'region') = 'country'    , avg(    { 1  }       Value),      If(   Valuelist(   'city'  ,  'country' , 'region') = 'region'  , avg(    { 1  }       Value) )))

Very useful for making comparaison !

JJ

Just suggestion - but I would put the valuelist expression into a variable, so that your dimension and expressions always use trhe same valuelist:

`Script:Set vValueList = Valuelist(   'city'  ,  'country' , 'region');Dimension\$(vValueList)Expression:=If(  \$(vValueList) = 'city' , avg(  Value) ,     If(   \$(vValueList) = 'country'    , avg(    { 1  <COUNTRY= P(COUNTRY) > }       Value),      If(   \$(vValueList) = 'region'  , avg(    { 1  <REGION= P(REGION) > }       Value))))`

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Thanks Jonathan

We will save a lot of time

JJ