Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

How to Change Text to Numeric

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?

Tags (2)
1 Solution

Accepted Solutions
Not applicable

Re: How to Change Text to Numeric

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.

3 Replies
mov
Esteemed Contributor III

Re: How to Change Text to Numeric

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

Re: How to Change Text to Numeric

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

Not applicable

Re: How to Change Text to Numeric

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.

Community Browser