Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Color coding rows in a pivot table

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

3 Replies
Anonymous
Not applicable
Author

See this post: Visual guides in a Pivot Table


The formula if(rowNo()/2=floor(rowNo()/2),LightBlue(),Blue()) seems to work fine

Anonymous
Not applicable
Author

The above expression works for me as well screen shot attached:

Thanks

Not applicable
Author

Thanks Timo and Chandrasheker for chiming in.

This expression works well when the hierarchy is not expanded. (as below)

1.JPG

However, when expanded, colors don't alternate row by row anymore. (as below)

2.JPG

Is there a way to provide color background guidance for big pivot tables however it's expanded or drilled down?

Thank you!