Qlik Community

Qlik Sense App Development

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

fasuhaibani
New Contributor III

Pivot Table Background Coloring when Expanded Only

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!

1 Solution

Accepted Solutions
MVP
MVP

Re: Pivot Table Background Coloring when Expanded Only

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

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
5 Replies
MVP
MVP

Re: Pivot Table Background Coloring when Expanded Only

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

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MVP
MVP

Re: Pivot Table Background Coloring when Expanded Only

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
rahulpawarb
Valued Contributor II

Re: Pivot Table Background Coloring when Expanded Only

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

fasuhaibani
New Contributor III

Re: Pivot Table Background Coloring when Expanded Only

Thanks!

fasuhaibani
New Contributor III

Re: Pivot Table Background Coloring when Expanded Only

Thanks!

Community Browser