topic Re: Re: Colormix on Pivot table (multiple expressions) in QlikView

Colormix on Pivot table (multiple expressions)

Mon, 18 Aug 2014 20:21:52 GMT

Hi everyone.

I needed assistance using colormix on a pivot table where the generated heat map fills in the colors horizontally across the Year dim found at the top. I can get colormix to work on a column vertically but could not find a setting to make it switch to calculating horizontally. Do I need to manually edit the generated code or is there another way to accomplish this?

Please refer to attached images. First shows it not working and in the latter I created it in excel to mimic what I required.

If you had a sample qvw that would really help. Would appreciate your assistance.

Thank you.

Re: Colormix on Pivot table (multiple expressions)

amit_saini — Mon, 18 Aug 2014 20:30:26 GMT

Hi QlikAsh13,

This may help you:

Background color of Pivot Table Labels

Thanks,

Re: Colormix on Pivot table (multiple expressions)

Anonymous — Mon, 18 Aug 2014 20:32:35 GMT

click on the dimension or expression for year and select background color.

in the definition for background color, input the expression that dictates the colors. something like:

=if(sum(Sales)<500000, rgb(255,0,0),

if(sum(Sales)>600000, rgb(0,255,0),

rgb(0,0,255)))

that should change the background color. modify the values and the expression itself to suit your logic. for colors, you can use argb() or color() or white()/red(), etc. instead of rgb().

Re: Colormix on Pivot table (multiple expressions)

Mon, 18 Aug 2014 20:47:40 GMT

Hi Amit.

Thank you for this.

I went through it but unfortunately it's similar to what I had originally done. The problem is that with these instances we are hard coding the min, intermediate and max rgb colours so it isn't mixing the colours like in a heat map for any values in between what we hard code.

Thanks.

Re: Colormix on Pivot table (multiple expressions)

Mon, 18 Aug 2014 20:48:36 GMT

Hi!

Thank you for this. Please refer to reply to Amit.

I tried this originally but it doesn't "mix" colours for values in between like in a heat map.

Thanks.

Re: Colormix on Pivot table (multiple expressions)

marthacano01 — Mon, 18 Aug 2014 20:53:01 GMT

I hope it helps you

Re: Colormix on Pivot table (multiple expressions)

Mon, 18 Aug 2014 21:06:05 GMT

Hi Martha.

Thank you.

Please refer to attached pic. You will find a blend of colours between red, yellow and green (heat map) which I can get to work on a straight table but applying this concept on a pivot where the data runs horizontally and not vertically doesn't seem to work. What you suggested is what I first did but then you lose the mix of in-between colours.

Thank you.

Re: Re: Colormix on Pivot table (multiple expressions)

marthacano01 — Mon, 18 Aug 2014 21:40:19 GMT

Attached is a new file. Just change the condition to use the Amount of Sales instead of the the years.

Re: Re: Colormix on Pivot table (multiple expressions)

jagan — Tue, 19 Aug 2014 03:20:55 GMT

Hi,

Please find attached file for solution. Hope it helps you.

Regards,

Jagan.

Re: Re: Colormix on Pivot table (multiple expressions)

Tue, 19 Aug 2014 07:57:25 GMT

Thank you Martha.

I suppose in addition to this, I can make it a bit more dynamic like what Jagan has also suggested.

Thanks once again.

Re: Re: Colormix on Pivot table (multiple expressions)

Tue, 19 Aug 2014 07:58:02 GMT

Hi Jagan!

Thank you for this.

Let me try it and revert...

Re: Re: Colormix on Pivot table (multiple expressions)

Wed, 20 Aug 2014 07:55:18 GMT

Thanks Jagan.

I think I just need to amend this slightly so three colours can be produced (red, amber, green).

Thank you.

Re: Re: Colormix on Pivot table (multiple expressions)

Wed, 20 Aug 2014 07:55:45 GMT

Thanks Jagan.

I think I just need to amend this slightly so three colours can be produced (red, amber, green).

Thank you.

Re: Colormix on Pivot table (multiple expressions)

camilo — Tue, 04 Nov 2014 19:41:59 GMT

Hi Jagan,

Is there any way of not fixing the color of the first row?

You used this expression:

=If(RowNo() = 1, RGB(255, 0, 0),

If([Sales] <= vAvgYearlySales, RGB(1* ([Sales]/1000), 0,0), RGB(0, 1* ([Sales]/1000), 0)))

If I delette the bold section, hopping to free the color of the first row QV pop up an error about exceeding the asigned memory...

Re: Colormix on Pivot table (multiple expressions)

jagan — Wed, 05 Nov 2014 01:51:32 GMT

Hi,

Try like this

=If(RowNo() = 1, RGB(255, 255, 255),

If([Sales] <= vAvgYearlySales, RGB(1* ([Sales]/1000), 0,0), RGB(0, 1* ([Sales]/1000), 0)))

RGB(255, 255, 255) - White the default color

Regards,

Jagan.