3 Replies Latest reply: Jun 3, 2016 3:34 PM by Ann Fang RSS

    Color coding rows in a pivot table

    Ann Fang

      Hi Qlik Community,

       

      When there are a lot of columns in a pivot table, it is hard to track a row from left to right across the screen. Is there anyway to color code the rows, from left to right, across the screen and to have the color to alternate from row to row?

       

      For example, the 1st, 3rd, 5th, ... rows would have white background color and 2nd, 4th, 6th.. rows would have blue color however the chart is expanded or collapsed.

       

      I tried "if(even(RowNo()),'white','lightblue')".

      This expression has 2 problems: 1. color only alternate on the lowest level; 2. empty rows are skipped within the column group. Therefore, color is not carried over from left to right and goes off sync.

      2.JPG

       

      I tried "if(even(RowNo(total)),'white','lightblue')". This is a bit better than the previous one because I'm starting to see more color alternation.

      However, this expression still has 2 problems. 1. color of expanded rows do not alternate (items with sign); 2. empty rows are skipped within the column group. Therefore, color is not carried over from left to right and goes off sync.

      1.JPG

       

       

      I also tried to use "if(even(Dimensionality()),'lightblue','white')". This is a little closer to the expected results as I'm getting consistent color alternation with expandable items. But items of the lowest level still do not have color alternation.

       

      3.JPG

       

      So I tried to enhance it a bit. However, I'm hitting the same wall as the cells are not colored consistently due to lack of data and it doesn't look very good.

       

      The formula I used is:

       

      if(NoOfRows() > 7, if(even(RowNo()),'white','lightblue'),

        if(fmod(Dimensionality(),2) = 1,'lightgrey',

          if(fmod(Dimensionality(),2) = 0, 'white','lightblue')

          )

      )

      So whenever there are more than 7 items listed, the color would alternate from row to row between white and lightblue.

      Otherwise, the color would alternate depending on the dimension.

      This way, it gives the report reader a bit more visual guidance.

       

      4.JPG

       

      A group has more than 7 rows so the color alternates. However, B group doesn't. Therefore, the color is not carried over from left to right.

      This seems to be the best I can get for now...

       

      NoOfRows() seems to only evaluate for within the scope of the current column group. NoOfRows(total) doesn't seem to work either because it still evaluates within the current column group...

       

      5.JPG

       

      Anyone have similar issues before?

      I'd appreciate any suggestions or workarounds. Thank you.

       

      Message was edited by: Ann Fang