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

19 Replies
antoniotiman
Master III
Master III

Yes is correct. You need to exclud Month 0 Value

See Attachment

paolojolly
Creator
Creator
Author

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

SEL1.png

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

SEL2.png

thank you again

paolojolly
Creator
Creator
Author

Attached file reproduce the problem

thank you

paolojolly
Creator
Creator
Author

Attached file reproduce the problem

thank you

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

paolojolly
Creator
Creator
Author

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

sunny_talwar

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


Capture.PNG

paolojolly
Creator
Creator
Author

Thanks also this solution works properly

Regards,

Paolo

paolojolly
Creator
Creator
Author

Thanks this solution works properly

Regards,

Paolo

sunny_talwar

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

Best,

Sunny