topic Re: color based on the average in pivot table in QlikView

color based on the average in pivot table

Thu, 02 Feb 2017 10:10:46 GMT

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 !

Re: color based on the average in pivot table

sunny_talwar — Thu, 02 Feb 2017 10:19:39 GMT

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())

Re: color based on the average in pivot table

Thu, 02 Feb 2017 11:14:23 GMT

there is an error...

attaching the qwv for example

Re: color based on the average in pivot table

sunny_talwar — Thu, 02 Feb 2017 12:05:12 GMT

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

Re: color based on the average in pivot table

Thu, 02 Feb 2017 12:28:08 GMT

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

Re: color based on the average in pivot table

sunny_talwar — Thu, 02 Feb 2017 12:41:35 GMT

Like this?

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

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())

Re: color based on the average in pivot table

stabben23 — Thu, 02 Feb 2017 12:45:06 GMT

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

Re: color based on the average in pivot table

Thu, 02 Feb 2017 12:48:22 GMT

thank you so much
very nice