Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

color based on the average in pivot table

Hello everyone, I need to highlight for each individual row values that rise or lower than 30% from the total of the line media.
The values that are shown in the table, the ratio of two fields:

(Sum ({<metername = {'EAT'}, assetnum = {'IT00 *'}, indicatoreEnergia = {'1'}>} [measurementvalue])
/
(Sum ({<TransType = {'reflui'}, indicatoreEnergia = {'1'}>} reflui_IN_OUT) -SUM ({<TransType = {'reflui'}, indicatoreEnergia = {'1'}>} bypass)))


can someone help me?
thank you !
qlik pivot.JPG

1 Solution

Accepted Solutions
sunny_talwar

Like this?

Capture.PNG

Background color expression:

=If(Kwh_Mc <= (Sum(TOTAL <location> {<metername = {'EAT'},assetnum={'IT00*'},indicatoreEnergia ={'1'}>} [measurementvalue])

/

(Sum(TOTAL <location> {<TransType={'reflui'},indicatoreEnergia ={'1'}>}reflui_IN_OUT)-Sum(TOTAL <location> {<TransType={'reflui'},indicatoreEnergia ={'1'}>}bypass))) * 0.70

or

Kwh_Mc >= (Sum(TOTAL <location> {<metername = {'EAT'},assetnum={'IT00*'},indicatoreEnergia ={'1'}>} [measurementvalue])

/

(Sum(TOTAL <location> {<TransType={'reflui'},indicatoreEnergia ={'1'}>}reflui_IN_OUT)-Sum(TOTAL <location> {<TransType={'reflui'},indicatoreEnergia ={'1'}>}bypass))) * 1.30, LightRed())

View solution in original post

7 Replies
sunny_talwar

Line media? is that Collocazione? May be this

If(

fabs(

(Sum ({<metername = {'EAT'}, assetnum = {'IT00 *'}, indicatoreEnergia = {'1'}>} [measurementvalue])

/

(Sum ({<TransType = {'reflui'}, indicatoreEnergia = {'1'}>} reflui_IN_OUT) -SUM ({<TransType = {'reflui'}, indicatoreEnergia = {'1'}>} bypass))),

/

(Sum(TOTAL <Collocazione> {<metername = {'EAT'}, assetnum = {'IT00 *'}, indicatoreEnergia = {'1'}>} [measurementvalue])

/

(Sum(TOTAL <Collocazione> {<TransType = {'reflui'}, indicatoreEnergia = {'1'}>} reflui_IN_OUT) -SUM ({<TransType = {'reflui'}, indicatoreEnergia = {'1'}>} bypass))) -1) >=0.30, Red(), Green())

Not applicable
Author

there is an error...


attaching  the qwv for example

sunny_talwar

I think it would be helpful if you can point out which cells are you hoping to see colored here?

Not applicable
Author

for example:
the first line (05F09D08) the total is 0.66 then the range of values included in the 30% above and below are 0.86 and 0.46. I would be interested to highlight the values that are not included in the range as in the attached image.
So for all rows.
thank you so much

sunny_talwar

Like this?

Capture.PNG

Background color expression:

=If(Kwh_Mc <= (Sum(TOTAL <location> {<metername = {'EAT'},assetnum={'IT00*'},indicatoreEnergia ={'1'}>} [measurementvalue])

/

(Sum(TOTAL <location> {<TransType={'reflui'},indicatoreEnergia ={'1'}>}reflui_IN_OUT)-Sum(TOTAL <location> {<TransType={'reflui'},indicatoreEnergia ={'1'}>}bypass))) * 0.70

or

Kwh_Mc >= (Sum(TOTAL <location> {<metername = {'EAT'},assetnum={'IT00*'},indicatoreEnergia ={'1'}>} [measurementvalue])

/

(Sum(TOTAL <location> {<TransType={'reflui'},indicatoreEnergia ={'1'}>}reflui_IN_OUT)-Sum(TOTAL <location> {<TransType={'reflui'},indicatoreEnergia ={'1'}>}bypass))) * 1.30, LightRed())

stabben23
Partner - Master
Partner - Master

Try this in Background color on Expression:

if(sum(aggr((sum({<metername = {'EAT'},assetnum={'IT00*'},indicatoreEnergia ={'1'}>} [measurementvalue])
/
(
sum({<TransType={'reflui'},indicatoreEnergia ={'1'}>}reflui_IN_OUT)-sum({<TransType={'reflui'},indicatoreEnergia ={'1'}>}bypass))),Year,location,Month))
>
sum(total <location> aggr((sum({<metername = {'EAT'},assetnum={'IT00*'},indicatoreEnergia ={'1'}>} [measurementvalue])
/
(
sum({<TransType={'reflui'},indicatoreEnergia ={'1'}>}reflui_IN_OUT)-sum({<TransType={'reflui'},indicatoreEnergia ={'1'}>}bypass))),location)),red(),green())

EDIT!! OK, I'll miss the 30% limit up and down

Not applicable
Author

thank you so much
very nice