Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

livio218526
Contributor

Colormix no data normalized

Hi everyone,

I want to use the colormix2 function, but the field in question is not normalized. Is there a way to be able to normalize my field within the colormix function?

I would like to have a gradient from red to green.


For example: -1 = Red, and 1 Green



Thanks,

Livio

1 Solution

Accepted Solutions
YoussefBelloum
Esteemed Contributor

Re: Colormix no data normalized

Try this on the background color expression:

ColorMix1 ((1+Sign(2*(Sum({<[Scenario_DB] = {$(=vPrimaryScen)}, [HFM.Year_DB] = {$(=vPrimaryYear)}, [HFM.Month] = {$(=vPrimaryMonth)}>}[HFM.Commercial Net Sales (DB)]/1000000)-RangeMin (top(total Sum({<[Scenario_DB] = {$(=vPrimaryScen)}, [HFM.Year_DB] = {$(=vPrimaryYear)}, [HFM.Month] = {$(=vPrimaryMonth)}>}[HFM.Commercial Net Sales (DB)]/1000000),1,NoOfRows(total))))/(RangeMax (top(total Sum({<[Scenario_DB] = {$(=vPrimaryScen)}, [HFM.Year_DB] = {$(=vPrimaryYear)}, [HFM.Month] = {$(=vPrimaryMonth)}>}[HFM.Commercial Net Sales (DB)]/1000000),1,NoOfRows(total)))-RangeMin (top(total Sum({<[Scenario_DB] = {$(=vPrimaryScen)}, [HFM.Year_DB] = {$(=vPrimaryYear)}, [HFM.Month] = {$(=vPrimaryMonth)}>}[HFM.Commercial Net Sales (DB)]/1000000),1,NoOfRows(total))))-1)*Sqrt(Fabs((2*(Sum({<[Scenario_DB] = {$(=vPrimaryScen)}, [HFM.Year_DB] = {$(=vPrimaryYear)}, [HFM.Month] = {$(=vPrimaryMonth)}>}[HFM.Commercial Net Sales (DB)]/1000000)-RangeMin (top(total Sum({<[Scenario_DB] = {$(=vPrimaryScen)}, [HFM.Year_DB] = {$(=vPrimaryYear)}, [HFM.Month] = {$(=vPrimaryMonth)}>}[HFM.Commercial Net Sales (DB)]/1000000),1,NoOfRows(total))))/(RangeMax (top(total Sum({<[Scenario_DB] = {$(=vPrimaryScen)}, [HFM.Year_DB] = {$(=vPrimaryYear)}, [HFM.Month] = {$(=vPrimaryMonth)}>}[HFM.Commercial Net Sales (DB)]/1000000),1,NoOfRows(total)))-RangeMin (top(total Sum({<[Scenario_DB] = {$(=vPrimaryScen)}, [HFM.Year_DB] = {$(=vPrimaryYear)}, [HFM.Month] = {$(=vPrimaryMonth)}>}[HFM.Commercial Net Sales (DB)]/1000000),1,NoOfRows(total))))-1))))/2, ARGB(255, 0, 128, 0), ARGB(255, 255, 0, 0))

7 Replies
YoussefBelloum
Esteemed Contributor

Re: Colormix no data normalized

if you don't master the syntax of colormix() function is would suggest you to try the colormix_wizard that you can find on Qlikview desktop

it will generate for you the code you need, just copy paste to qliksense.

It will save you a lot of time

livio218526
Contributor

Re: Colormix no data normalized

I'm using Qlik Sense

YoussefBelloum
Esteemed Contributor

Re: Colormix no data normalized

Yes I Know, it is just a suggestion.

it is worth installing qlikview desktop to generate the colormix expression if you don't know how to write it

livio218526
Contributor

Re: Colormix no data normalized

Are you able to generate for me the colormix function?

my value is:

Sum({<[Scenario_DB] = {$(=vPrimaryScen)}, [HFM.Year_DB] = {$(=vPrimaryYear)}, [HFM.Month] = {$(=vPrimaryMonth)}>}[HFM.Commercial Net Sales (DB)]/1000000)  AS x

and i want a gradient color from Red to Green.

Thanks,

Livio

YoussefBelloum
Esteemed Contributor

Re: Colormix no data normalized

Try this on the background color expression:

ColorMix1 ((1+Sign(2*(Sum({<[Scenario_DB] = {$(=vPrimaryScen)}, [HFM.Year_DB] = {$(=vPrimaryYear)}, [HFM.Month] = {$(=vPrimaryMonth)}>}[HFM.Commercial Net Sales (DB)]/1000000)-RangeMin (top(total Sum({<[Scenario_DB] = {$(=vPrimaryScen)}, [HFM.Year_DB] = {$(=vPrimaryYear)}, [HFM.Month] = {$(=vPrimaryMonth)}>}[HFM.Commercial Net Sales (DB)]/1000000),1,NoOfRows(total))))/(RangeMax (top(total Sum({<[Scenario_DB] = {$(=vPrimaryScen)}, [HFM.Year_DB] = {$(=vPrimaryYear)}, [HFM.Month] = {$(=vPrimaryMonth)}>}[HFM.Commercial Net Sales (DB)]/1000000),1,NoOfRows(total)))-RangeMin (top(total Sum({<[Scenario_DB] = {$(=vPrimaryScen)}, [HFM.Year_DB] = {$(=vPrimaryYear)}, [HFM.Month] = {$(=vPrimaryMonth)}>}[HFM.Commercial Net Sales (DB)]/1000000),1,NoOfRows(total))))-1)*Sqrt(Fabs((2*(Sum({<[Scenario_DB] = {$(=vPrimaryScen)}, [HFM.Year_DB] = {$(=vPrimaryYear)}, [HFM.Month] = {$(=vPrimaryMonth)}>}[HFM.Commercial Net Sales (DB)]/1000000)-RangeMin (top(total Sum({<[Scenario_DB] = {$(=vPrimaryScen)}, [HFM.Year_DB] = {$(=vPrimaryYear)}, [HFM.Month] = {$(=vPrimaryMonth)}>}[HFM.Commercial Net Sales (DB)]/1000000),1,NoOfRows(total))))/(RangeMax (top(total Sum({<[Scenario_DB] = {$(=vPrimaryScen)}, [HFM.Year_DB] = {$(=vPrimaryYear)}, [HFM.Month] = {$(=vPrimaryMonth)}>}[HFM.Commercial Net Sales (DB)]/1000000),1,NoOfRows(total)))-RangeMin (top(total Sum({<[Scenario_DB] = {$(=vPrimaryScen)}, [HFM.Year_DB] = {$(=vPrimaryYear)}, [HFM.Month] = {$(=vPrimaryMonth)}>}[HFM.Commercial Net Sales (DB)]/1000000),1,NoOfRows(total))))-1))))/2, ARGB(255, 0, 128, 0), ARGB(255, 255, 0, 0))

livio218526
Contributor

Re: Colormix no data normalized

Thanks

YoussefBelloum
Esteemed Contributor

Re: Colormix no data normalized

You're welcome

Good luck