13 Replies Latest reply: Apr 19, 2018 10:32 AM by Antonio Domingos RSS

    Color a Pivot Table

    Antonio Domingos

      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.

              • Re: Color a Pivot Table
                Antonio Domingos

                Hi Jonathan!!!

                 

                Man, you worked hard here, thank you so much for your help, I understand everything you created.

                 

                One question, I see the gradient between highers and lowers is for the columns but the correct is for the row.

                It's complicated to apply by rows not columns?

                 

                Thanks again!!!

                  • Re: Color a Pivot Table
                    Jonathan Vitale

                    Oh man, I really thought that what I did was what you were asking for. It's unfortunate that we didn't understand each other - that was a lot of work.

                     

                    I don't think you could take the same approach for the rows to create a min and max value for a color range. It's too complicated for me to explain, but the "before" function would make it very difficult, if not impossible.

                     

                    My one suggestion is somehow to change the data model with the data load editor so that you somehow "move up" the data that is needed at a given time point to the current time point. So instead of calling before(..) you'd have a field with the same information like "previous_data". If you have that it will be much easier to find the min/max.

                     

                    If you get that, let me know and I'll help.

                      • Re: Color a Pivot Table
                        Antonio Domingos

                        Hi Jonathan! I'm trying to find a solution to not use before.

                        Can you think Can I use something :

                        sum({$<DATA_BASE={"$(=DATE(MAX(DATA_BASE,2)))"} >} RISCO_A_EM_DIA))

                         

                        Actually I tried with no sucess.

                         

                        Tks

                        • Re: Color a Pivot Table
                          Antonio Domingos

                          Hi Jonathan!

                           

                          I found one way to remove the BEFORE, I created a new field that brings me what I want.

                           

                          Follow the app attached.

                           

                          Do you think is better to solve?

                           

                          Thanks a lot!!!

                            • Re: Color a Pivot Table
                              Jonathan Vitale

                              Great,

                               

                              Can you update the version that I uploaded, which has the measures as variables? It will be a lot easier to work with.

                               

                              Jonathan

                                • Re: Color a Pivot Table
                                  Antonio Domingos

                                  Hi Jonathan,

                                   

                                  Here it go!!!

                                   

                                  Only the ninth row and last 2 I couldn't find a way to remove before.

                                  Maybe to color them I can force the values and colors.

                                   

                                  Thank you so much!!

                                    • Re: Color a Pivot Table
                                      Jonathan Vitale

                                      Okay, so here's what it looks like:

                                      Capture.PNG

                                      I updated the variables to reflect the new measures without the "before" function. I couldn't do so with rows 9, 13, or 14, so they don't have a color.

                                       

                                      Here's what the formula looks like in each:

                                      If ( ($(vM1) - Min(total Aggr($(vM1), DATA_BASE))) / (Max(total Aggr($(vM1), DATA_BASE)) - Min(total Aggr($(vM1), DATA_BASE))) < 0.5, 
                                      ColorMix1(
                                      ($(vM1) - Min(total Aggr($(vM1), DATA_BASE))) / (Max(total Aggr($(vM1), DATA_BASE)) - Min(total Aggr($(vM1), DATA_BASE))) * 2
                                              , $(vColor_Low), $(vColor_Mid)
                                          ),
                                          ColorMix1(
                                      (($(vM1) - Min(total Aggr($(vM1), DATA_BASE))) / (Max(total Aggr($(vM1), DATA_BASE)) - Min(total Aggr($(vM1), DATA_BASE))) - 0.5) * 2
                                              , $(vColor_Mid), $(vColor_High)
                                          )
                                      )
                                      

                                       

                                      The interesting part here is the Aggr expressions. Basically, what I am doing is asking Qlik to apply my measure $(vM1) to each value of the DATA_BASE dimension. Then I take either the Min or the Max of these values. I need the keyword "total" in the Min or Max function to ensure that the AGGR function will work on all values of DATA_BASE and not just the one we are in at this cell value.

                                       

                                      Let me know if you have any questions about how this works. Hopefully, this will solve the problem.

                          • Re: Color a Pivot Table
                            kaan erisen

                            Hi Antonio,

                             

                            If you want to colored the values every row separately. You can use the expression below for all measures on pivot table.

                             

                            ColorMix2( (hrank(total

                            {{Measure Expression}}

                            )/(NoOfColumns(TOTAL)/2))-1 ,red(), green(),Yellow())

                             

                            Untitled.png

                             

                            With that expression, every row color value calculated individually. Highest value is red, lowest value is green and the other values in the row is gradient over yellow.