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: 
paolojolly
Creator
Creator

Highlight max and min values in pivot table

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

1 Solution

Accepted Solutions
antoniotiman
Master III
Master III

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

View solution in original post

19 Replies
sunny_talwar

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

Capture.PNG

paolojolly
Creator
Creator
Author

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

Immagine.png

antoniotiman
Master III
Master III

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

sunny_talwar

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

paolojolly
Creator
Creator
Author

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

antoniotiman
Master III
Master III

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

paolojolly
Creator
Creator
Author

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

sunny_talwar

Can you try this may be

Capture.PNG

paolojolly
Creator
Creator
Author

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

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