Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a problem with in a pivot table where I am using this formula to alternate rows colors, but when there is a null row value it doesn't work well.
Screen to see my problem
How can I resolve?
FORMULA:
= if(
Dimensionality()= 0,
$(vTotalPivotColor),
if (Dimensionality()= 1,
if (even(RowNo(total))=0, $(vAlternate1PivotColor),
if (even(RowNo(total))=-1, $(vAlternate2PivotColor),
if (isnull(even(RowNo(total))),
$(vAlternate1PivotColor)))),
if((Dimensionality()> 1),$(vExpandPivotColor))))
where variables are RGB colors
Hello!
This is due to the fact that a pivot table is merely a visual representation of a straight table in memory. Try selecting "View Data" in the chart's context (right-click) menu to see how the data really "looks".
RowNo() is calculated from the "straight" table. Not from the pivot visual representation of it; that's why it is seldom useful in pivot tables.
You may try to use if(even(fieldindex('FieldName',Fieldname))=0,blue()) to at least avoid alternating colors in cells belonging to the same pivot "row".
"FieldName" must be one of the dimensions you are using as "rows" in the pivot table. Experiment with this and you may find a satisfactory look; the appearance will vary depending on the expansion state of your pivot table and the presence of null values.