topic Re: Conditional Colors Extension in Integration, Extension & APIs

Conditional Colors Extension

antoniodneto — Fri, 06 Apr 2018 17:38:49 GMT

Hello Guys,

I'm trying to color my table following a conditional to highers is red, medium is yellow and lowes green.

The rows have different measures.

You guys know if have any extension to do that?

I'm not getting by expression, colormix, ifs, etc...

Tks a lot

Re: Conditional Colors Extension

jonvitale — Wed, 11 Apr 2018 01:13:18 GMT

Are you just trying to color the background of cells in a table? You can do that with the out-of-the-box table.

Re: Conditional Colors Extension

antoniodneto — Fri, 15 Mar 2019 15:01:27 GMT

Hello Jonathan,

I'm trying something like that

Re: Conditional Colors Extension

jonvitale — Wed, 11 Apr 2018 12:56:46 GMT

You should be able to do this using the background color for each column in the table.

Use the ColorMix function to create a gradient from 0 to 100 (or 1)

Maybe this post is relevant (it's about pivot tables, but the same concept should apply):

Gradients in pivot table

Re: Conditional Colors Extension

m_s — Fri, 13 Apr 2018 14:33:43 GMT

Hello Antonio,

would a heatmap fulfill your requirements?

If yes maybe try out http://branch.qlik.com/#!/project/56728f52d1e497241ae6983e (free) or the VizLib Heatmap (free up to 5 users iirc) .

Cheers,

Mathias

Re: Conditional Colors Extension

antoniodneto — Fri, 13 Apr 2018 16:37:20 GMT

Hello Mathias! It's possible attach the files here? Because Github is blocked in my company.

Tks a lot!

Re: Conditional Colors Extension

m_s — Mon, 16 Apr 2018 07:48:30 GMT

Here you go.

Mathias

Re: Conditional Colors Extension

antoniodneto — Mon, 16 Apr 2018 12:26:28 GMT

Hi Mathias, is not working.

Any another solution?

Re: Conditional Colors Extension

antoniodneto — Mon, 16 Apr 2018 12:52:17 GMT

I got!! But I can't use.

I need to use 2 dimensions, but I have just one

Is one dimension (DATA) and some measures in the rows.

Tks!

Re: Conditional Colors Extension

m_s — Mon, 16 Apr 2018 13:03:11 GMT

Hi Antonio, have you tried Jonathan's suggestion?

Mathias

Re: Conditional Colors Extension

antoniodneto — Mon, 16 Apr 2018 13:10:11 GMT

Yeah but doesn't work well.

Because each row has your measure, and the values are different between them. If I specify my higher value and lower for each row, I should "fix" the measure every month to apply the colors.

Sounds clear?

Tks!

Re: Conditional Colors Extension

jonvitale — Mon, 16 Apr 2018 14:31:20 GMT

Man, I don't hear that nearly enough.

Re: Conditional Colors Extension

antoniodneto — Fri, 15 Mar 2019 15:06:05 GMT

Hi Jonathan!

Yeah I know is confuse. I attached my app to understand. Could you help me?

Tks a lot!

Re: Conditional Colors Extension

jonvitale — Mon, 16 Apr 2018 16:27:32 GMT

Antonio,

Perhaps I see the problem. In excel you get the ability to color by column. In this case, you have one measure per row. Coloring the background of a measure colors the row, not the column. Does that sound right?

What I want to understand is this:

1) Do you want the color be on an absolute scale? For example, 10% is always red, 90% is always green, 50% is always yellow? (or similar)

2) Do you want a relative color scale based on the values for a specific date? For example, if the highest value on Jul/15 is 12.7% that color should be green, and the lowest value 1.6% should be red? (Or similar, perhaps with a wider range, like 0 to 20%).

3) Or, do you want a relative color scale based on the values for a single measure. For example, if the highest value on "Over 30" is 11.2% that should be green. The lowest value, 8.7%, should be red. (or similar).

1 and 3 are easy. I'm not sure about how to do 2, but I could think about it.

Jonathan

Re: Conditional Colors Extension

antoniodneto — Mon, 16 Apr 2018 16:59:25 GMT

Hi Jonathan tks for your answer.

So I think for me is the second way.

I need to color row by row, where the highest value will be red, medium yellow and lowest in green.

The third can be as well. Can I use differents expressions to color, not a single to all rows ?

If you could help me, I really need help to handle it.

Thank you so much!

Re: Conditional Colors Extension

jonvitale — Tue, 17 Apr 2018 13:31:54 GMT

I took a look and it is very challenging because you have measures like this:

SUM(RISCO_E_31_60)/before(sum({1}RISCO_A_EM_DIA),2)

Which use the before function in the pivot table to look at previous columns (I've never actually used "before" before). This makes it difficult to both look at the previous column and all the columns in order to get a color range.

Is there any way you could get rewrite this:

before(sum({1}RISCO_A_EM_DIA),2)

so that it doesn't rely upon placement in the pivot table? Something like:

sum({<DATA_BASE={*the-previous-column-value*}}>}RISCO_A_EM_DIA)

With this it would be easier to create a color range.

Re: Conditional Colors Extension

antoniodneto — Tue, 17 Apr 2018 13:37:55 GMT

Hi Jonathan, feel free to make any adjust on the expression.

I used before because was my unique idea to calc that.

If I can use sum({<DATA_BASE={*the-previous-column-value*}}>}RISCO_A_EM_DIA) to get the previous value is correct for me.

Thank you so much!

Re: Conditional Colors Extension

jonvitale — Tue, 17 Apr 2018 14:02:34 GMT

Antonio,

Actually, it's more complicated than I made it sound because I wouldn't know what the "previous-column-value" is. I'd have to somehow look it up based upon the current column value within a set analysis.

Maybe I can try to give this some thought and come back to it, but I have to go do my own work.

Here's my suggestion: Since we are now talking about standards Qlik visualizations (Pivot Table) and not an extension, you should open a new thread in the Qlik Sense App Development place. There are folks monitoring those boards more closely and can figure out the complicated expression you might need in order to color your table the way you like.

Try to give them as much information as possible, including the measure definition with the before statement. If need be, I'll help clarify over on the other thread. Just @ me so I know where to find you.

Re: Conditional Colors Extension

antoniodneto — Tue, 17 Apr 2018 14:21:31 GMT

Hi Jonathan, Ok I really understand you, anyway thanks for your time and patience.

I creating a new discuss in the group you suggested.

Thanks again!