Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ivanyang25
New Contributor III

How to define background color of auto-sum row in Pivot Table?

Dear QS Experts


I am new to QS and I have a question about color by expression

I have 2 sheets (‘Sample’ & ‘Target’) and linked via ‘Region’ column in QS as below

01.PNG


Then I create a simple pivot table with auto-sum row and set background color with expression. The measurement is Make% ---- Count({<[Met]={'1'}>}[Order Line])/Count([Order Line]

01.PNG

If Make% meet the target in ‘Target’ sheet then it shows green otherwise green. Color expression as below

01.PNG

There is no sum target in the target sheet and I am not allowed to change raw data

But sum target calculation method is as below:

Q2:  ((HK(0.63)+TH(0.65)+INDO(0.72)+JAPAN(0.63))/4=0.65

Q3:  ((HK(0.65)+TH(0.67)+INDO(0.75)+JAPAN(0.65))/4=0.68


All the rows work well except the ‘Auto-Sum’ row. As you can see 61% is lower than above sum Q2 & Q3 target but it still shows green color

May I know how to adjust my expression to make it right?

You could check attached raw data and apps for your reference

Thanks for your advice in advance

Ivan

1 Solution

Accepted Solutions
pradosh_thakur
Honored Contributor II

Re: How to define background color of auto-sum row in Pivot Table?

Hi

i have updated your app but hard coded the value 0.68 and 0.65 in the background color . change that value as per your requirement and it shall give you the result. if stuck somewhere tell me the  exact expression for back ground color of sum row i will update it.

edit :inplace of 0.65 and 0.68 put the expression

regards

Pradosh

Learning never stops.
11 Replies
pradosh_thakur
Honored Contributor II

Re: How to define background color of auto-sum row in Pivot Table?

Hi

i have updated your app but hard coded the value 0.68 and 0.65 in the background color . change that value as per your requirement and it shall give you the result. if stuck somewhere tell me the  exact expression for back ground color of sum row i will update it.

edit :inplace of 0.65 and 0.68 put the expression

regards

Pradosh

Learning never stops.
ivanyang25
New Contributor III

Re: How to define background color of auto-sum row in Pivot Table?

Pradosh

Thank you so much for the reply

It works well so far

May I know what does dimensionality()=0 mean?

Best Regards
Ivan

pradosh_thakur
Honored Contributor II

Re: How to define background color of auto-sum row in Pivot Table?

dimensionality is always 0 for summary rows. try putting an extra column with expression dimesnionality() and you will know what it returns.

regards

Pradosh

Learning never stops.
ivanyang25
New Contributor III

Re: How to define background color of auto-sum row in Pivot Table?

Pradosh

I am still little bit confused about 'dimensionality is always 0'. I try your way and result like below

Does it mean that result equals to auto sum function? If so, why the color turn back to green again?

捕获.PNG

Best Regards

Ivan

pradosh_thakur
Honored Contributor II

Re: How to define background color of auto-sum row in Pivot Table?

whats your expression for summary row target value ?

can you post that ?

let me have a look at it. if it is les then your calulated value it will turn green else red. thats what happened woth o.68 and 0.65 . change it and you will see the diffrence

regards

Pradosh

Learning never stops.
ivanyang25
New Contributor III

Re: How to define background color of auto-sum row in Pivot Table?

Pradosh

Please kindly check the attahced apps for your reference

Best Regards

Ivan

pradosh_thakur
Honored Contributor II

Re: How to define background color of auto-sum row in Pivot Table?

this is not the same app we were using ?

Learning never stops.
ivanyang25
New Contributor III

Re: How to define background color of auto-sum row in Pivot Table?

Pradosh

I just add dimesnionality() as you instructed. Color expression keeps unchanged

Best Regards

Ivan

pradosh_thakur
Honored Contributor II

Re: How to define background color of auto-sum row in Pivot Table?

I have added the dimensionality column for your reference.Hope it will be helpful.

regards

Pradosh

Learning never stops.
Community Browser