topic Complex calculated dimension in QlikView

Complex calculated dimension

Fri, 30 Sep 2011 18:31:32 GMT

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

Complex calculated dimension

Mon, 03 Oct 2011 18:27:18 GMT

Any ideas or it's definitly not possible

Complex calculated dimension

Tue, 04 Oct 2011 18:39:36 GMT

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 <COUNTRY= P(COUNTRY) > }       Value),
      If(   Valuelist(   'city' , 'country' , 'region') = 'region' , avg(    { 1 <REGION= P(REGION) > }       Value) )))

Very useful for making comparaison !

Re: Complex calculated dimension

jonathandienst — Tue, 04 Oct 2011 20:11:27 GMT

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

Complex calculated dimension

Thu, 06 Oct 2011 17:59:54 GMT

Thanks Jonathan

We will save a lot of time