Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead 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  <COUNTRY= P(COUNTRY) > }       Value),

      If(   Valuelist(   'city'  ,  'country' , 'region') = 'region'  , avg(    { 1  <REGION= P(REGION) > }       Value) )))

Very useful for making comparaison !

View solution in original post

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  <COUNTRY= P(COUNTRY) > }       Value),

      If(   Valuelist(   'city'  ,  'country' , 'region') = 'region'  , avg(    { 1  <REGION= P(REGION) > }       Value) )))

Very useful for making comparaison !

View solution in original post

jonathandienst

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