topic Re: Highlighting Multiple Cells in a Straight Table in QlikView

Highlighting Multiple Cells in a Straight Table

Wed, 05 Nov 2014 20:27:31 GMT

Hey. I'm pretty new to Qlikview and I have a straight table where I want to highlight the minimum and maximum values in a column. I have been able to successfully highlight one or the other using a background color expression, but I can't seem to figure out how to do both.

Any help would be greatly appreciated.

Thanks.

Re: Highlighting Multiple Cells in a Straight Table

ecolomer — Wed, 05 Nov 2014 21:19:27 GMT

If you need to convert in Bold put '<B>' in Format Text option similar to Background Colour

Re: Highlighting Multiple Cells in a Straight Table

Peter_Cammaert — Wed, 05 Nov 2014 21:20:40 GMT

How about this example?

Re: Highlighting Multiple Cells in a Straight Table

Peter_Cammaert — Wed, 05 Nov 2014 21:37:10 GMT

Care to figure out what I have done in my document? You can easily apply the same technique to the example you attached to your OP a moment ago...

Re: Highlighting Multiple Cells in a Straight Table

Wed, 05 Nov 2014 21:40:09 GMT

Thanks for the reply. I'm trying to apply it right now. I think I'm running into some syntax errors because of the formula I was using. Hopefully I can sort those out in a second.

Re: Highlighting Multiple Cells in a Straight Table

Peter_Cammaert — Wed, 05 Nov 2014 21:43:29 GMT

I admit that applying the aggr() function is not the easiest thing to do in QV. But probably one of the most powerful at the same time, so ask away if you're stuck.

Re: Highlighting Multiple Cells in a Straight Table

Wed, 05 Nov 2014 21:47:48 GMT

Thank you very much. I've never used that particular function so I'm not sure of exactly what I'm trying to accomplish with it. Would you mind giving me a synopsis?

Additionally, the formula I applied on my actual document utilized the Rank() function and set analysis. I'm not sure if this is relevant or not, but just in case it is helpful.

Re: Highlighting Multiple Cells in a Straight Table

Wed, 05 Nov 2014 21:58:48 GMT

You know what, this gave me exactly what I needed. Thanks so much. I didn't wind up using the Aggr() function but I had misused the IF() function before that and your example helped me pin down my syntax.

Thanks again.

Re: Highlighting Multiple Cells in a Straight Table

Wed, 05 Nov 2014 21:59:46 GMT

This will also be nice. Thank you.

Re: Highlighting Multiple Cells in a Straight Table

Peter_Cammaert — Wed, 05 Nov 2014 22:03:27 GMT

My example contains something like this for the background color (let's assume you only want the maximum value):

=IF (The_current_Sum = Max(All_Sums_in_this_column), Set_the_color_to_Green)

The_current_Sum is just a repeat of the expression formula.

For All_sums_in_this_column we have to get a series of cell values in the current column. For that we use the aggr() function that will create a Virtuall table with the dimensions listed starting from parameter 2 onwards, and a single expression consisting of parameter 1. When we've obtained this series, we apply the Max function and compare the current cell value to the maximum of the series.

Normally, aggr() will only operate within the limits of the current dimension values. We force aggr() to operate on the whole table by specifying the TOTAL keyword, which is shorthand for set analysis that includes all current dimension values in the current table.

Hope this makes things clearer. But as I said, aggr() is pretty advanced QV technology.

Best,

Peter

Re: Highlighting Multiple Cells in a Straight Table

Peter_Cammaert — Wed, 05 Nov 2014 22:05:57 GMT

Oh well, glad I could help