icCube - InterpolateRGBColors based on min & max values?

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 ?

Solution

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