Qlik Community

App Development

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

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
aknabe1
Contributor II
Contributor II

Dynamic Pivot Table Background Color based on Dimensionality

Is it possible to compare the second level of a pivot table (dimensionality = 2) to its parent value (dimensionality = 1).  In the image below, I am looking to compare the values that fall under "consumer packaging" to values that fall underneath. For example, compare APAC (0.00) to Consumer Packaging (15.79), Europe (33.05) to Consumer Packaging (15.79), etc... 

I have tried using the formula below but need to compare to a dynamic value opposed to a constant ( >20). 

 

=if(Dimensionality() = 2, if(( Test) > 20 ,green(), red()))

 

 

aknabe1_0-1652296116914.png

 

Labels (1)
1 Solution

Accepted Solutions
vinieme12
Champion III
Champion III

As below 

=if(Dimensionality() = 2, if( AVG(measure)>AVG(total<dimension 1>measure) ,green(), red()))

 

Replace AVG and measure with whatever your expression is 

Replace dimension1 with whatever your first dimension name is

 

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

4 Replies
edwin
Master
Master

if what you want is to compare the result for the parent against the child, you can use aggregate.  and assuming you dont want the parent highlighted:

if(Dimensionality()>1, if( yourExpressionHere > AGGR(yourExpressionHere, parentFieldHere), green(), red()))

for example if your fields are CATEGORY as dimension1 and MEASURE as your value:

if(Dimensionality()>1, if(avg(MEASURE) > AGGR( avg(MEASURE), [CATEGORY]), green(), red()))

hope that makes sense

edwin
Master
Master

of course you need to decide if its a >= comparison - whats the color when the same

vinieme12
Champion III
Champion III

As below 

=if(Dimensionality() = 2, if( AVG(measure)>AVG(total<dimension 1>measure) ,green(), red()))

 

Replace AVG and measure with whatever your expression is 

Replace dimension1 with whatever your first dimension name is

 

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
aknabe1
Contributor II
Contributor II
Author

Thank you this worked! I noticed I had to use field values and not measures.