topic Re: Pivot Table Background Coloring when Expanded Only in App Development

Pivot Table Background Coloring when Expanded Only

Anonymous — Mon, 30 Jan 2017 05:54:53 GMT

Hello everyone!

I have been working with Pivot Tables lately, and I was asked to color some pivot tables cells based on a measure value. For example:

Having the following table:

ID , Name , Duration

1 , Joe , 10

2 , Lee , 11

3 , Joe , 12

4 , Lee , 13

5 , Joe , 14

6 , Lee , 15

7 , Joe , 16

8 , Lee , 17

Now every duration greater or equals 14 should be colored red, otherwise green, and here is my expression:

if(Duration >= 14, red(), Green() ) )

My issue is when the table is collapsed, the sum of Joe and Lee will be RED. What I want is for the coloring to appear when expanding the table only.

Thanks!

jonathandienst — Mon, 30 Jan 2017 06:12:36 GMT

Use Dimensionality() to apply the colour coding to the bottom level expressions only.

=If(Dimensionality() = 3 And Duration >= 14, Red(), Green())

jonathandienst — Mon, 30 Jan 2017 06:16:31 GMT

Or if you want the colour coding to work based on the largest value and rolled up to the higher dimension values:

=If(Max(Aggr(Duration, ID, Name)) > = 14, Red() , Green())

If Duration is an expression, then replace the "Duration" in the above expression with the Duration expression.

rahulpawarb — Mon, 30 Jan 2017 06:20:02 GMT

Hello Feras,

Trust that you are doing well!

For Duration, define the Background Color expression as If(Dimensionality()=2,if(Duration >= 14, red(), Green()))

Also refer the attached application file.

Hope this will be helpful.

Regards!

Rahul

Anonymous — Mon, 30 Jan 2017 06:23:21 GMT

Thanks!

Anonymous — Mon, 30 Jan 2017 06:23:26 GMT

Thanks!

sharmi1990 — Tue, 13 Dec 2022 03:16:33 GMT

Why should the dimensionality() be = 3?