Color coding rows in a pivot table

[#1]

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.

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.

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.

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.

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...

Anyone have similar issues before?

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

Message was edited by: Ann Fang

[Anonymous]

See this post: Visual guides in a Pivot Table‌

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

[Anonymous]

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

Thanks

[#4]

Thanks Timo and Chandrasheker for chiming in.

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

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

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

Thank you!