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

Highlight max and min values in pivot table

pljsoftware — Tue, 08 Nov 2011 13:55:56 GMT

Hi all!

I have a Pivot Table with many dimensions and one expression. Is it possible to highlight the cells that contain max and min expression values, depending on the current aggregations?

Kindest Regards.

Re: Highlight max and min values in pivot table

its_anandrjs — Tue, 08 Nov 2011 14:17:55 GMT

Hi,

Write some thing like this in background properties of the expressions by click on plus sign.

If( Sum(Sales) = Max(Sales) ,Yellow(),

If( Sum(Sales) = Min(Sales) ,Green() ))

Rgds

Anand

Highlight max and min values in pivot table

pljsoftware — Tue, 08 Nov 2011 14:41:51 GMT

No, it doesn't works. Your example draws all the cells with a yellow background.

Highlight max and min values in pivot table

its_anandrjs — Tue, 08 Nov 2011 18:55:54 GMT

Hi,

Can you share any sample file it goes so easy.

Rgds

Anand

Highlight max and min values in pivot table

Wed, 09 Nov 2011 06:01:14 GMT

1.go to chart properties of pivot table and go to tab visual cues.

2.select expression and give upper limit and lower limit.

3.Then the values between upper and lower limit will be highlighted.

try it out.

regards

Highlight max and min values in pivot table

kamalqlik — Wed, 09 Nov 2011 06:11:29 GMT

You can do one more thing..

Go in the visual cues tab....Here yo u will find the Upper and Lower Value....you can fix the color according to your reqirement

Re: Highlight max and min values in pivot table

Anonymous — Mon, 13 Aug 2012 15:49:50 GMT

I have the same question. But I ordered the values ascendant by this expression:

min(aggr(count(Field), Dimension 1, Dimension 2))

But doesn't order. I want min values on the top of the table so I can highlight them and the max values down.

Re: Highlight max and min values in pivot table

Tue, 14 Aug 2012 06:52:26 GMT

Please attach the application. We all can help you out with that!

Re: Highlight max and min values in pivot table

Tue, 14 Aug 2012 06:56:12 GMT

Hi,

Write some thing like this in background properties of the expressions by click on plus sign.

If( Sum(Sales) = Max(Sales) OR Sum(Sales) = Min(Sales) ,Yellow())

Hope this helps!

Thanks,

Attitude

Re: Highlight max and min values in pivot table

Anonymous — Fri, 17 Aug 2012 17:07:09 GMT

If( Sum(Sales) = Max(Sales) OR Sum(Sales) = Min(Sales) ,Yellow()) doesn't work.

This is the example. I want max and min values by Field2.

Re: Highlight max and min values in pivot table

sebastiandperei — Sat, 18 Aug 2012 15:51:47 GMT

Hi, try:

If(sum(Aggr(Sum(Data),Field2))=max(Aggr(sum(Data),Field2)), yellow())

In background expression.

Re: Highlight max and min values in pivot table

sebastiandperei — Sat, 18 Aug 2012 15:53:00 GMT

If you invert the order of fields, you must use Field1 instead of Field2.

If you add more dimentions, use the one that is in horizontal axis.

Re: Highlight max and min values in pivot table

pljsoftware — Sat, 18 Aug 2012 16:46:09 GMT

Hi all,

we have a pivot table with these dimensions

Week (Settimana)

Data (Date)

Ora (Hour)

Expresssion: QtaPasseggeri (quantity of passengers)

with espression sum(QtaPasseggeri) and expression name "Passeggeri".

The solution for this problem is to set this expression in Background Color

if([Passeggeri]=max(total aggr(sum(QtaPasseggeri), Settimana, Data, Ore),1), rgb(255,0,0),

if([Passeggeri]=max(total aggr(sum(QtaPasseggeri), Settimana, Data, Ore),2), rgb(200,0,0),

if([Passeggeri]=max(total aggr(sum(QtaPasseggeri), Settimana, Data, Ore),3), red(),

IF([Passeggeri]>800,

RGB(0,0,128),

IF([Passeggeri]>400 AND [Passeggeri]<800,

RGB(0,204,255),

IF([Passeggeri]>200 AND [Passeggeri]<400, RGB(204, 255, 255))

)

Regards

Luca Jonathan Panetta

PLJ Software

Re: Highlight max and min values in pivot table

sebastiandperei — Sat, 18 Aug 2012 22:05:35 GMT

Are you kidding me? You used my solution and you mark as answered to yourself?

Very ungrateful...

Re: Highlight max and min values in pivot table

Anonymous — Mon, 20 Aug 2012 12:57:04 GMT

That expression doesn't mark the max values. You see, 12454 is less than 68732 in the column b.

Re: Highlight max and min values in pivot table

sebastiandperei — Mon, 20 Aug 2012 15:46:52 GMT

Dejalo Romina, si después se marca como respondido a él mismo...

Re: Highlight max and min values in pivot table

pljsoftware — Mon, 20 Aug 2012 23:33:24 GMT

Sorry, but this solution is the solution that I have found afther my original post.

Only I don't remeber the post and I haven't post the solution.

Luca Jonathan Panetta

PLJ Software

Re: Highlight max and min values in pivot table

pljsoftware — Mon, 20 Aug 2012 23:44:58 GMT

Sebastian,

me duele mucho que piensas que me marco la respuesta como correcta simplemente por agarrar puntos. Esta es la solución que ultilize por solucionar mi problema.

Si lo deseas puedo mandarte uno scheenshot de mi trabajo que si miras la data del post es de 8 noviembre 2011 y la solución es del 9 o 10 como mucho.

Lo siento mucho no haber contestato antes a esta mi misma pregunta pero me había olvidado de su existencia.

Marco también tu respuesta como correcta ya que la pusiste antes de mí.

PS: No pensar que ya que soy italiano hablo sólo inglés.

Saludos

Luca Jonathan Panetta

PLJ Software

Re: Highlight max and min values in pivot table

pljsoftware — Mon, 20 Aug 2012 23:49:51 GMT

Hello Romina,

I don't know the test file, I can say you that my expression work fine in my job.

Sebastians's solution is the same of my solution but in my case I have more Max to mark.

Regards

Luca Jonathan Panetta

PLJ Software

Re: Highlight max and min values in pivot table

sebastiandperei — Tue, 21 Aug 2012 10:18:12 GMT

Luca, lo mismo dije antes en Inglés, no me acuses que subestimar tu capacidad de lenguaje porque no fue lo que quise hacer. Si así se entendió, te pido disculpas.

Por otro lado, me parece bien que hayas encontrado la solución.

Saludos,

Sebastián.