7 Replies Latest reply: Feb 15, 2018 11:13 AM by youssef belloum RSS

    Colormix no data normalized

    Livio Sebastiano Colombo

      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

        • Re: Colormix no data normalized
          youssef belloum

          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

            • Re: Colormix no data normalized
              Livio Sebastiano Colombo

              I'm using Qlik Sense

                • Re: Colormix no data normalized
                  youssef belloum

                  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

                    • Re: Colormix no data normalized
                      Livio Sebastiano Colombo

                      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

                        • Re: Colormix no data normalized
                          youssef belloum

                          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))