How to Change Text to Numeric

[#1]

Hello,

Here is my current formula, but it is not working because of the Rank function:

colormix1(Rank( TOTAL $(variablecolumn), 4, 1)/(Count({1}TOTAL Distinct $(variablecolumn))+1), RGB(0,155,225), RGB(255,255,255))

What I am trying to do is:

1) The user can select any column as "variablecolumn".

2) The chart would then use "variablecolumn" as the second dimension

3) The color of the second dimension would be a gradient based on "variablecolumn", so if there are 5 values, they would order the values (by number or alphabet depending on the field) and then use colormix to create the gradient.

So I am using Rank to number the variablecolumn fields, but I want the color to be consistent across the first dimension, but I'm having a hard time.

Any ideas?

[Anonymous]

I think that num(rank(...)) can help, give it a try.

[avinashelite]

try with NUM#() or num() function ....

[#4 · Accepted solution]

Okay Num() didn't work, but I figured out how to get what I want:

colormix1(Rank( TOTAL $(variablecolumn), 1, 1) /(Max(TOTAL Aggr(NUM(Rank( TOTAL $(variablecolumn), 1, 1)), Submission, $(variablecolumn)))+1), RGB(0,155,225), RGB(255,255,255))

This creates a smooth gradient, no matter how many distinct values there are.