Pivot table - colouring total rows without using Dimensionality?

gramqlik — Sat, 16 Nov 2024 18:24:32 GMT

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:

Or better yet, with less shading:

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.

topic Pivot table - colouring total rows without using Dimensionality? in App Development

Pivot table - colouring total rows without using Dimensionality?