topic Re: Highlight max and min values in pivot table in QlikView

Highlight max and min values in pivot table

paolojolly — Wed, 25 Nov 2020 16:16:04 GMT

Hi, I need help to correct highlight max and min values in pivot table

In attached document i use in background color setting

if(isnull(sum(W_TOT_KG)) <> -1,
if(sum(W_TOT_KG) = Max(total aggr(sum(W_TOT_KG), MESE_DOC, ANNO_DOC)), rgb(0,255,0),
If(IsNull(sum(W_TOT_KG) ) <> -1 and sum(W_TOT_KG) <> 0,
If(sum(W_TOT_KG) = Min(total aggr(
If(ISNULL(sum(W_TOT_KG)) <> -1 AND sum(W_TOT_KG) <> 0,
sum(W_TOT_KG)
)
,MESE_DOC, ANNO_DOC)), rgb(255,0,0))
)
)
)

but it does not always work

Can someone help me?

Thank you very much

Re: Highlight max and min values in pivot table

sunny_talwar — Wed, 06 Jul 2016 14:31:29 GMT

Seems to be working, no? What is the issue here?

Re: Highlight max and min values in pivot table

paolojolly — Wed, 06 Jul 2016 14:36:58 GMT

in this case it works, but sometimes it does not highlight the values correctly.

Re: Highlight max and min values in pivot table

antoniotiman — Wed, 06 Jul 2016 15:18:08 GMT

Hi Paolo,

Your problem is MESE_Doc = 0.

Change Dimension

If(MESE_Doc > 0,MESE_DOC)

and Check 'Suppress when value is Null'

In Background Color Setting :

If(RangeMin(Top(Sum(W_TOT_KG),1,NoOfRows()))=Sum(W_TOT_KG),Red(),If(RangeMax(Top(Sum(W_TOT_KG),1,NoOfRows()))=Sum(W_TOT_KG),Green()))

See Attachment

Regards,

Antonio

Re: Highlight max and min values in pivot table

sunny_talwar — Wed, 06 Jul 2016 15:43:12 GMT

What selections have you made here? I am unable to replicate the problem at my end

Re: Highlight max and min values in pivot table

paolojolly — Wed, 06 Jul 2016 16:21:25 GMT

Thank you, your solution highlight the maximum and minimum values for each column.

I need to highlight only the cell with the maximum value of the entire pivot and

the cell with the minimum value of the entire pivot

Thank you very much

Re: Highlight max and min values in pivot table

antoniotiman — Wed, 06 Jul 2016 16:39:04 GMT

Try

If(Sum(W_TOT_KG)=Min({<W_TOT_KG-={0}>} TOTAL Aggr(Sum(W_TOT_KG),ANNO_DOC,MESE_DOC)),Red(),If(Sum(W_TOT_KG)=Max(TOTAL Aggr(Sum(W_TOT_KG),ANNO_DOC,MESE_DOC)),Green()))

Re: Highlight max and min values in pivot table

paolojolly — Wed, 06 Jul 2016 17:04:02 GMT

The problem occurs randomly and do not understand when. I think there is something wrong
in the formula I use in Background Color Setting when I have so many records.

The problem occurs on a very heavy QlikView document and I find
it hard to reproduce in a test version

Thank you very much

Re: Highlight max and min values in pivot table

sunny_talwar — Wed, 06 Jul 2016 17:06:14 GMT

Can you try this may be

Re: Highlight max and min values in pivot table

paolojolly — Wed, 06 Jul 2016 17:07:35 GMT

Sign - between Min({<W_TOT_KG and ={0}>} is correct?

I remove it and I tried your new solution, but not always work

Re: Highlight max and min values in pivot table

antoniotiman — Wed, 06 Jul 2016 17:24:04 GMT

Yes is correct. You need to exclud Month 0 Value

See Attachment

Re: Highlight max and min values in pivot table

paolojolly — Wed, 06 Jul 2016 20:59:32 GMT

Thank you. your work is useful but I do not understand why, if I have only one filter I get a result like the following

with cells of future month with value zero colored in red(not correct) and when i select another filter result become correct

thank you again

Re: Highlight max and min values in pivot table

paolojolly — Thu, 07 Jul 2016 06:56:15 GMT

Attached file reproduce the problem

thank you

Re: Highlight max and min values in pivot table

paolojolly — Thu, 07 Jul 2016 07:00:04 GMT

Attached file reproduce the problem

thank you

Re: Highlight max and min values in pivot table

antoniotiman — Thu, 07 Jul 2016 07:33:23 GMT

Try

If(Sum(W_TOT_KG)=Min({<MESE_DOC={"=Sum(W_TOT_KG) > 0"}>} TOTAL Aggr(Sum(W_TOT_KG),ANNO_DOC,MESE_DOC)),Red(),If(Sum(W_TOT_KG)=Max(TOTAL Aggr(Sum(W_TOT_KG),ANNO_DOC,MESE_DOC)),Green()))

Re: Highlight max and min values in pivot table

paolojolly — Thu, 07 Jul 2016 08:02:35 GMT

thank you.

I have another problem.if you look at the attached file , if I do not have any active filter this solution does not show correctly the max value

thanks

Re: Highlight max and min values in pivot table

sunny_talwar — Thu, 07 Jul 2016 12:24:04 GMT

Can you check this expression:

If(Sum(W_TOT_KG)=Min(TOTAL Aggr(If(Sum(W_TOT_KG) > 0, Sum(W_TOT_KG)),ANNO_DOC,MESE_DOC)),Red(),

If(Sum(W_TOT_KG)=Max(TOTAL Aggr(Sum(W_TOT_KG),ANNO_DOC,MESE_DOC)),Green()))

Re: Highlight max and min values in pivot table

paolojolly — Thu, 07 Jul 2016 16:27:52 GMT

Thanks also this solution works properly

Regards,

Paolo

Re: Highlight max and min values in pivot table

paolojolly — Thu, 07 Jul 2016 16:28:33 GMT

Thanks this solution works properly

Regards,

Paolo

Re: Highlight max and min values in pivot table

sunny_talwar — Thu, 07 Jul 2016 16:29:54 GMT

We are glad you finally found what you were looking for .

Best,

Sunny