11 Replies Latest reply: May 25, 2011 1:37 PM by Damien Coueron RSS

    Dimension background color problem in pivot ? bug ?

    Damien Coueron

      Hello every one,

       

      With Qlikview 10 SR2, i'm trying to assign a different background color to a dimension depending on the values of the dimension but when the expression of the graph returns no value, its associated dimension cell remains white.

      I've read some posts about this problem and it was said to uncheck "Suppress Zero-Value" and check "Populate missing values". I've done this and it doesn't change a thing in my case.

       

      Can any one help me with this ?

       

      The expression set in "background color" option of the dimension is as follows :

       

      =If (Alpha = 'A', RGB(100, 150, 250),
       If (Alpha = 'B', RGB(90, 160, 250),
       If (Alpha = 'C', RGB(80, 170, 250),
       If (Alpha = 'D', RGB(70, 180, 250),
       If (Alpha = 'E', RGB(60, 190, 250),
       If (Alpha = 'F', RGB(50, 200, 250),
       If (Alpha = 'G', RGB(40, 210, 250),
       If (Alpha = 'H', RGB(30, 220, 250),
       If (Alpha = 'I', RGB(20, 230, 250),
       If (Alpha = 'J', RGB(10, 240, 250),
      ))))))))))
      

       

      I have also attached an example file which shows the problem.

       

      The problem is highlighted in the following picture, Alpha = 'D' is not colored because the first expression value (Dim22 = 'a') is empty.

      test.jpeg

       

      Regards,

      Damien

       

      Ce message a été modifié par: dcoueron

        • Re: Dimension background color problem in pivot ? bug ?
          =If (RowNo()= 1, RGB(100, 150, 250),
           If (RowNo()= 2, RGB(90, 160, 250),
           If (RowNo()= 3, RGB(80, 170, 250),
           If (RowNo()= 4, RGB(70, 180, 250),
           If (RowNo()= 5, RGB(60, 190, 250),
           If (RowNo()= 6, RGB(50, 200, 250),
           If (RowNo()= 7, RGB(40, 210, 250),
           If (RowNo()= 8, RGB(30, 220, 250),
           If (RowNo()= 9, RGB(20, 230, 250),
           If (RowNo()= 10, RGB(10, 240, 250),
          ))))))))))
          

          With RowNo() the cell is colored even if the first value of Dim22 is empty. I'm not sure that's exactly what you need, but it works in this case

           

          test_dc.png

          • Dimension background color problem in pivot ? bug ?

            I changed the background color expression as below

             

            =If (Alpha = 'A', RGB(100, 150, 250),

            If (Alpha = 'B', RGB(90, 160, 250),

            If (Alpha = 'C', RGB(80, 170, 250),

            If (Alpha = 'D' or IsNull(Alpha), RGB(70, 180, 250),

            If (Alpha = 'E', RGB(60, 190, 250),

            If (Alpha = 'F', RGB(50, 200, 250),

            If (Alpha = 'G', RGB(40, 210, 250),

            If (Alpha = 'H', RGB(30, 220, 250),

            If (Alpha = 'I', RGB(20, 230, 250),

            If (Alpha = 'J', RGB(10, 240, 250),

            ))))))))))

            • Dimension background color problem in pivot ? bug ?
              Marcos Gomez

              i had that problem some time ago, and the support line told me that this case was a WAD (Work As Design). The only solution is, as aiklamha says, put a specific color for the value that fails, but this is a solution very dificult to mainteain if the dimension data often vary.

               

              If anyone knows how to solve it, i also welcome the information.

              • Dimension background color problem in pivot ? bug ?
                Damien Coueron

                Thank you all for your answers.

                 

                In my case the "or IsNull(Alpha)" solution will not work as there is more than one value that fails (D and G for example).

                 

                And the solution with RowNo() will work until there is a selection made on the Alpha dimension. In this case, the rowno won't be the same any more.

                  • Re: Dimension background color problem in pivot ? bug ?
                    Damien Coueron

                    I think i found a solution which works even when selections are made on the dimension.

                     

                    The Background color setting for the Alpha dimension is as follows :

                     

                    =If (SubField(GetFieldSelections(Alpha, ',', 26), ',', RowNo()) = 'A' Or (GetAlternativeCount(Alpha) = 0 And RowNo() = 1), RGB(100, 150, 250),
                     If (SubField(GetFieldSelections(Alpha, ',', 26), ',', RowNo()) = 'B' Or (GetAlternativeCount(Alpha) = 0 And RowNo() = 2), RGB(90, 160, 250),
                     If (SubField(GetFieldSelections(Alpha, ',', 26), ',', RowNo()) = 'C' Or (GetAlternativeCount(Alpha) = 0 And RowNo() = 3), RGB(80, 170, 250),
                     If (SubField(GetFieldSelections(Alpha, ',', 26), ',', RowNo()) = 'D' Or (GetAlternativeCount(Alpha) = 0 And RowNo() = 4), RGB(70, 180, 250),
                     If (SubField(GetFieldSelections(Alpha, ',', 26), ',', RowNo()) = 'E' Or (GetAlternativeCount(Alpha) = 0 And RowNo() = 5), RGB(60, 190, 250),
                     If (SubField(GetFieldSelections(Alpha, ',', 26), ',', RowNo()) = 'F' Or (GetAlternativeCount(Alpha) = 0 And RowNo() = 6), RGB(50, 200, 250),
                     If (SubField(GetFieldSelections(Alpha, ',', 26), ',', RowNo()) = 'G' Or (GetAlternativeCount(Alpha) = 0 And RowNo() = 7), RGB(40, 210, 250),
                     If (SubField(GetFieldSelections(Alpha, ',', 26), ',', RowNo()) = 'H' Or (GetAlternativeCount(Alpha) = 0 And RowNo() = 8), RGB(30, 220, 250),
                     If (SubField(GetFieldSelections(Alpha, ',', 26), ',', RowNo()) = 'I' Or (GetAlternativeCount(Alpha) = 0 And RowNo() = 9), RGB(20, 230, 250),
                     If (SubField(GetFieldSelections(Alpha, ',', 26), ',', RowNo()) = 'J' Or (GetAlternativeCount(Alpha) = 0 And RowNo() = 10), RGB(10, 240, 250),
                    ))))))))))
                    

                     

                    A warning so : this solution won't work if the order of the dimension values in the pivot table changes. 

                     

                    Please find attached an example application.

                     

                    Regards,

                    Damien

                      • Re: Dimension background color problem in pivot ? bug ?
                        Damien Coueron

                        Unfortunately, this solution doesn't work if a selection is made on a field which have an impact on the number of possible values for dimension Alpha but no direct selection is made on Alpha.

                          • Re: Dimension background color problem in pivot ? bug ?
                            John Witherspoon

                            This won't work in every case, but it works for this specific case because there are SOME values out there for Alpha = D.  Maybe it'll be good enough for your actual application.

                             

                            =If(only({1} total <Alpha> Alpha) = 'A', RGB(100, 150, 250),

                            If (only({1} total <Alpha> Alpha) = 'B', RGB(90, 160, 250),

                            If (only({1} total <Alpha> Alpha) = 'C', RGB(80, 170, 250),

                            If (only({1} total <Alpha> Alpha) = 'D', RGB(70, 180, 250),

                            If (only({1} total <Alpha> Alpha) = 'E', RGB(60, 190, 250),

                            If (only({1} total <Alpha> Alpha) = 'F', RGB(50, 200, 250),

                            If (only({1} total <Alpha> Alpha) = 'G', RGB(40, 210, 250),

                            If (only({1} total <Alpha> Alpha) = 'H', RGB(30, 220, 250),

                            If (only({1} total <Alpha> Alpha) = 'I', RGB(20, 230, 250),

                            If (only({1} total <Alpha> Alpha) = 'J', RGB(10, 240, 250),

                            ))))))))))

                             

                             

                            Edit:  Or not.  Just noticed that you have a "show all values" in there.  So anything for which there is no selected data at all won't get the color.

                             

                            Edit:  Fixed with set analysis.

                              • Re: Dimension background color problem in pivot ? bug ?
                                John Witherspoon

                                Here's another approach.  Use a data island for the dimension, and use sum(if()) to link it to your data.  It'll cause performance problems on large data sets of course, but works even if there is NO data that matches in the entire database, and you're showing all values so that it's still visible.

                                 

                                AlphaIsland:

                                LOAD text(fieldvalue('Alpha',recno())) as Alpha2

                                AUTOGENERATE fieldvaluecount('Alpha');

                                CONCATENATE (AlphaIsland) // to demonstrate coloring with no data

                                LOAD '[' as Alpha2

                                AUTOGENERATE 1;

                                 

                                 

                                 

                                dimension 1 = Alpha2
                                dimension 2 = dim22
                                expression  = sum(if(Alpha=Alpha2,Expression32))

                                 

                                =If(Alpha2 = 'A', RGB(100, 150, 250),
                                If (Alpha2 = 'B', RGB(90, 160, 250),
                                If (Alpha2 = 'C', RGB(80, 170, 250),
                                If (Alpha2 = 'D', RGB(70, 180, 250),
                                If (Alpha2 = 'E', RGB(60, 190, 250),
                                If (Alpha2 = 'F', RGB(50, 200, 250),
                                If (Alpha2 = 'G', RGB(40, 210, 250),
                                If (Alpha2 = 'H', RGB(30, 220, 250),
                                If (Alpha2 = 'I', RGB(20, 230, 250),
                                If (Alpha2 = 'J', RGB(10, 240, 250),
                                if (Alpha2 = '[', lightgray() // to demonstrate coloring with no data
                                )))))))))))

                                  • Re: Dimension background color problem in pivot ? bug ?
                                    Damien Coueron

                                    Thank you John for your answers.

                                     

                                    I tried your first solution because our dataset is quite big and i don't want to use a data island if i don't have to.

                                    I also had a problem with your first solution because no matter what selection i make on the Alpha dimension, every value is always shown in the pivot table.

                                     

                                    In the mean time i came up with an other formula which does what i need (but i think it could be more resources consuming) :

                                     

                                     IF(only({$ <Alpha={'A'}>} TOTAL aggr(rank(TOTAL 26-ord(Alpha)), Alpha)) = RowNo(TOTAL), RGB(100, 150, 250),
                                     IF(only({$ <Alpha={'B'}>} TOTAL aggr(rank(TOTAL 26-ord(Alpha)), Alpha)) = RowNo(TOTAL), RGB(90, 160, 250),
                                     IF(only({$ <Alpha={'C'}>} TOTAL aggr(rank(TOTAL 26-ord(Alpha)), Alpha)) = RowNo(TOTAL), RGB(80, 170, 250),
                                     IF(only({$ <Alpha={'D'}>} TOTAL aggr(rank(TOTAL 26-ord(Alpha)), Alpha)) = RowNo(TOTAL), RGB(70, 180, 250),
                                     IF(only({$ <Alpha={'E'}>} TOTAL aggr(rank(TOTAL 26-ord(Alpha)), Alpha)) = RowNo(TOTAL), RGB(60, 190, 250),
                                     IF(only({$ <Alpha={'F'}>} TOTAL aggr(rank(TOTAL 26-ord(Alpha)), Alpha)) = RowNo(TOTAL), RGB(50, 200, 250),
                                     IF(only({$ <Alpha={'G'}>} TOTAL aggr(rank(TOTAL 26-ord(Alpha)), Alpha)) = RowNo(TOTAL), RGB(40, 210, 250),
                                     IF(only({$ <Alpha={'H'}>} TOTAL aggr(rank(TOTAL 26-ord(Alpha)), Alpha)) = RowNo(TOTAL), RGB(30, 220, 250),
                                     IF(only({$ <Alpha={'I'}>} TOTAL aggr(rank(TOTAL 26-ord(Alpha)), Alpha)) = RowNo(TOTAL), RGB(20, 230, 250),
                                     IF(only({$ <Alpha={'J'}>} TOTAL aggr(rank(TOTAL 26-ord(Alpha)), Alpha)) = RowNo(TOTAL), RGB(10, 240, 250)
                                    ))))))))))
                                    

                                     

                                    Note : I doesn't work if the order of the pivot table is not the dimension order but it's okay for me.

                                     

                                    Thanks every one !

                                      • Re: Dimension background color problem in pivot ? bug ?
                                        John Witherspoon

                                        I assumed you wanted to show all values because you explicitly checkmarked "show all values" in your pivot table.  If you don'twant to show all values, remove the "show all values" checkmark and remove the {1} I added when I noticed you had "show all values" checkmarked.

                                         

                                        =If(only(total <Alpha> Alpha) = 'A', RGB(100, 150, 250),
                                        If (only(total <Alpha> Alpha) = 'B', RGB(90, 160, 250),
                                        If (only(total <Alpha> Alpha) = 'C', RGB(80, 170, 250),
                                        If (only(total <Alpha> Alpha) = 'D', RGB(70, 180, 250),
                                        If (only(total <Alpha> Alpha) = 'E', RGB(60, 190, 250),
                                        If (only(total <Alpha> Alpha) = 'F', RGB(50, 200, 250),
                                        If (only(total <Alpha> Alpha) = 'G', RGB(40, 210, 250),
                                        If (only(total <Alpha> Alpha) = 'H', RGB(30, 220, 250),
                                        If (only(total <Alpha> Alpha) = 'I', RGB(20, 230, 250),
                                        If (only(total <Alpha> Alpha) = 'J', RGB(10, 240, 250),
                                        ))))))))))