icCube - InterpolateRGBColors based on min & max values?

Question

I understand that InterpolateRGBColors function is returning a color by position of value between 0 and 1... So its seems to be doable only with percentages, not numbers...

Is there a way to have the same functionality, but based on the min and max values returned in a set ?

What I want is to attribute colors to my measure but in a range of min([Measures].[NbSejours]) to max([Measures].[NbSejours]) ( not 0 to 1)...

WITH 
MEMBER [Measures].[color] AS 
    InterpolateRGBColors(
        [Measures].[NbSejours] 
        ,rgb(176,224,230)
        ,rgb(135,206,235)
        ,rgb(0,191,255)
        ,rgb(100,149,237)
        ,rgb(0,0,255)
        ,rgb(0,0,139)
        ,rgb(25,25,112) 
    ), BACK_COLOR=currentCellValue()
SELECT 
  {
    {[Measures].[NbSejours]}
   ,[Measures].[color]
  } ON COLUMNS
 ,{
    NonEmpty
    (
      [Etablissement].[Etablissement].[Etablissement].ALLMEMBERS
     ,[Measures].[NbSejours]
    )
  } ON ROWS
FROM 
(
  SELECT 
    {{[Periode].[Periode].[All-M].&[2013]}} ON 0
  FROM [Cube]
)
CELL PROPERTIES 
  STYLE
 ,CLASSNAME
 ,VALUE
 ,FORMATTED_VALUE;

Is there a way to do that ?

(Interesting question: I'm guessing the `icCube` guys will pick this one up to help you) — whytheq, Feb 04 '15 at 10:45
Im not ICcube expert, but common sense says you should just try RANK() function outputs for the whole set and then get number for yours. — George, Feb 04 '15 at 11:30

score 3 · Accepted Answer · answered Feb 05 '15 at 08:46

InterpolateRGBColors expect a numerical between 0 and 1 for interpolation. So we need to scale our measure to ensure we get the right colors.

There is an example in our live demo , here.

What we need is to scale [Measures].[NbSejours] between 0,1. There are two no documented function in icCube DistributionFlat & DistributionRank.

A non efficient version

WITH
  SET [AxisX] AS  NonEmpty([Etablissement].Etablissement].Etablissement].ALLMEMBERS,[Measures].[NbSejours])
  FUNCTION distr(x_) as  DistributionFlat(  [AxisX], [Measures].[NbSejours], x_ )
  MEMBER [Measures].[color] AS 
InterpolateRGBColors(
    distr([Measures].[NbSejours]) 
    ,rgb(176,224,230)
    ,rgb(135,206,235)
    ,rgb(0,191,255)
    ,rgb(100,149,237)
    ,rgb(0,0,255)
    ,rgb(0,0,139)
    ,rgb(25,25,112) 
), BACK_COLOR=currentCellValue()
....

Once I got a bit of time I'll write a version using Vectors (here and here) that is more performant as in the example above we calculate every time the values for the set.

Hope it helps

Thanks a lot @ic3! This is exactly what I need in terms of functionality... But have to admit this is not efficient... :-( — Bertrand Miot, Feb 06 '15 at 11:24
Try adding this to the first line of your MDX //#Cache( mode = 'ALL', maxSize = 42000 ) — ic3, Feb 06 '15 at 16:28

score 0 · Answer 2 · answered Feb 04 '15 at 19:42

I don'r know icCube so the following might not work, even though I have used standard functions. As @George commented you can use the standard RANK function to find each members relative position.

You will need to feed that value into the definition of [Measures].[color]...

WITH 
SET [estMembersOrdered] AS
  ORDER(
      [Etablissement].[Etablissement].[Etablissement].ALLMEMBERS
     ,[Measures].[NbSejours]
     ,BDESC
  )
MEMBER [Measures].[rnkEtablissement] AS 
   RANK(
     [Etablissement].[Etablissement].CURRENTMEMBER
     , [estMembersOrdered]
   )
MEMBER [Measures].[color] AS 
    InterpolateRGBColors(
        [Measures].[NbSejours] 
        ,rgb(176,224,230)
        ,rgb(135,206,235)
        ,rgb(0,191,255)
        ,rgb(100,149,237)
        ,rgb(0,0,255)
        ,rgb(0,0,139)
        ,rgb(25,25,112) 
    ), BACK_COLOR=currentCellValue()
SELECT 
  {
    {[Measures].[NbSejours]}
   ,[Measures].[color]
   ,[Measures].[rnkEtablissement] 
  } ON COLUMNS
 ,{
    NonEmpty
    (
      [Etablissement].[Etablissement].[Etablissement].ALLMEMBERS
     ,[Measures].[NbSejours]
    )
  } ON ROWS
FROM 
(
  SELECT 
    {{[Periode].[Periode].[All-M].&[2013]}} ON 0
  FROM [Cube]
)
CELL PROPERTIES 
  STYLE
 ,CLASSNAME
 ,VALUE
 ,FORMATTED_VALUE;

icCube - InterpolateRGBColors based on min & max values?

2 Answers2