Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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 !

jonathandienst
Partner - Champion III
Partner - Champion III

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