Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 !
Any ideas or it's definitly not possible
JJ
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 !
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
Thanks Jonathan
We will save a lot of time
JJ