Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
gramqlik
Contributor III
Contributor III

Pivot table - colouring total rows without using Dimensionality?

Hi,

I've been asked to apply background colour to the Total and subtotal rows in a fully expanded pivot table.

Using Dimensionality() on the key dimension and measure fields, I can to get it to apply b/g colour to the word "Totals" in the Row Fields, as well as to the Value totals.

IF(Dimensionality()=0, RGB(223, 223, 230), IF(Dimensionality()=2, RGB(233, 230, 233), White()))

 

Ideally, I'd like the colour to be applied to the whole row, commencing with the word "Totals" and including the blank cells at the top of the subsequent Row Fields. This built-in Excel design approximates what I mean:

1.png

Or better yet, with less shading:

2.png

More important than the specifics of the shading, though - if I change the order of the Row Fields (as a user may), or move a row field so it becomes a column field, the b/g colour is either lost altogether, or becomes incorrectly applied (unlike the Excel example). This seems to be because moving a field changes its dimensionality number.

So... is there any other way to do this?

I'm aiming to get the grand total row to be the darkest shade, the next field with a total row should be a medium shade and the last (right-most) row with a total should be a lighter shade, no matter what those fields are.

I've played about with Dimensionality(), SecondaryDimensionality(), RowNo() and ColumnNo() to no avail.

Is this just a limitation of the QS pivot table that we have to live with for now, or is there a way around it? I expect there are better pivot tables out there, but I am not able to add any unsupported extensions to our Qlik environment.

 

Cheers.

Labels (3)
0 Replies