topic How to Change Text to Numeric in QlikView

How to Change Text to Numeric

Fri, 06 Feb 2015 01:29:22 GMT

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?

Re: How to Change Text to Numeric

Anonymous — Fri, 06 Feb 2015 01:39:17 GMT

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

Re: How to Change Text to Numeric

avinashelite — Fri, 06 Feb 2015 04:31:50 GMT

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

Re: How to Change Text to Numeric

Fri, 06 Feb 2015 18:06:36 GMT

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.