7 Replies Latest reply: Apr 18, 2018 9:12 PM by Jonathan Vitale RSS

    Color a Pivot Table

    Antonio Domingos Neto

      Hello Guys.

       

      I need your help.

       

      I have one pivot table where DATA_BASE is my Dimension, and some rows.

      Each row has your specific expression to calc.

       

      I'm trying to find a way to color the values in the row, where highers in red,medium yellow and lowers in green. Making a gradient among them.

       

      My app is attached to understand my view.

       

      Thank you so much guys!!!

        • Re: Color a Pivot Table
          Jonathan Vitale

          A couple notes:

          1. This is a continuation of a discussion from over here in the extensions streams. I suggested moving it over because it probably can be solved with traditional pivot tables.

          https://community.qlik.com/message/1472624

           

          2. Antonio really want to color based upon the range in the column. So, a low value in a column might get a red color, a medium value might get yellow, and a high value might get green. However, this is a challenge because it's the rows that are measures in this case. It's easier to base the color of a cell on its range within the measure. Not sure how this can be done across different measures.

           

          3. One more complication. Antonio is using the "before" function to look up the value in the prior column. This seems to make it hard to calculate a max and min to base a color range upon. Here's an example of one of his measures (representing a row).

           

          (SUM(RISCO_C_11_14)+SUM(RISCO_D_15_30)) / before(sum({1}RISCO_A_EM_DIA))

          • Re: Color a Pivot Table
            Jonathan Vitale

            Antonio,

             

            I had a little time to work on this. I came up with a solution that I think should work for you. It is not very elegant, but it works. Here's what the table looks like (part of it is cut off).

            84df9afe94774abfa3ef484b064579ae.png

            Notice that the color ranges over the columns. So, the lowest value in a column will be red, while the highest will be green. Here's how I did it:

             

            1. Create a variable for each of your measures in the table. Each one of these variables, M1 through M14 is an exact copy-and-paste of the measure you wrote in the table.

             

            Capture.PNG

            2. Create vM1_M12_Max and vM1_M12_Min, which are the max and min value for each of the 12 measures. You can see how they are defined in the image above. Note: If you want to include or remove a measure, you'll have to update these variable definitions.

             

            3. Write a background color expression for each measure that finds the relative value of the measure (on the given day) compared to the min and max of all other measures. To get the red-yellow-green color scale I had to split the ColorMix1 expression into two haves: red-to-yellow for low (< 0.5) and yellow-to-green for high (>= 0.5). The colors are defined in variables so they can be easily adjusted. Here's the code for the first measure, all other measures only replace the vM1 part:

            If ( ($(vM1) - $(vM1_M12_Min)) / ($(vM1_M12_Max) - $(vM1_M12_Min)) < 0.5,
            ColorMix1(
            ($(vM1) - $(vM1_M12_Min)) / ($(vM1_M12_Max) - $(vM1_M12_Min)) * 2
                    , $(vColor_Low), $(vColor_Mid)
                ),
                ColorMix1(
            (($(vM1) - $(vM1_M12_Min)) / ($(vM1_M12_Max) - $(vM1_M12_Min)) - 0.5) * 2
                    , $(vColor_Mid), $(vColor_High)
                )
            )
            

             

            Hopefully, this is everything you need. It took some work. Good luck.