Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
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
I'm using Qlik Sense
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
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
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))
Thanks
You're welcome
Good luck