topic Re: Mark only highest value under a limit - background color expression in QlikView

Mark only highest value under a limit - background color expression

Zwax — Sat, 16 Nov 2024 19:51:17 GMT

Hi,

Is it possible to mark the highest number under a limit? (background color expression)

Lets say you have a bunch of different numbers:

500,

452,

35,

816

How do I mark the highest number under ley say 480 (=452) without marking all the numbers under the “limit” ?

I have tried some stuff with the max and maxstring, but no luck when it’s under a limit.

If(Test<='480',LightBlue(),) //<-- Marks all equal to or under

if(max(Test)<= '480',LightBlue(),) //<--do not work – makes sense since the max value is not under

If(Test<='480',if(Test=max(Test),LightBlue(),),) //<--Do not work

if(Test< =max(If(Test <='480', Test,)),LightBlue(),) //<--Do not work

Re: Mark only highest value under a limit - background color expression

Victor_Alumanah — Mon, 21 Oct 2019 10:27:45 GMT

Try this

If(Rank(YourExpression) = 1,Blue(), Green())

Re: Mark only highest value under a limit - background color expression

Zwax — Mon, 21 Oct 2019 10:35:22 GMT

Thats pretty cool 🙂 Marks all rows above one color and equal to or under another color.

However in this case I would like to only mark one row (max string under the limit).

Re: Mark only highest value under a limit - background color expression

Victor_Alumanah — Mon, 21 Oct 2019 10:58:41 GMT

would this work

If(Rank(YourExpression) = 1,Blue())

Re: Mark only highest value under a limit - background color expression

Zwax — Mon, 21 Oct 2019 11:37:47 GMT

I guess I'm using it the wrong way.

How would you make it work, to just highlight one (highest within the limit)? Could you give me an example?

Right now I'm marking all - either above or under the limit.

Re: Mark only highest value under a limit - background color expression

sunny_talwar — Mon, 21 Oct 2019 11:43:47 GMT

How about this

If(Max(TOTAL {<Test = {"<480"}>} Test) = Test, LightBlue())

Re: Mark only highest value under a limit - background color expression

Zwax — Mon, 21 Oct 2019 12:01:22 GMT

Maybe... 🙂

I get red lines under the part starting with {"<480"}

If(Max(TOTAL {<Test = {"<480"}>} Test) = Test, LightBlue())

Re: Mark only highest value under a limit - background color expression

sunny_talwar — Mon, 21 Oct 2019 12:04:20 GMT

I hope you have used the correct field name as I think Test is just a placeholder? There are three Tests that need to be replaced by your field name....

Re: Mark only highest value under a limit - background color expression

Zwax — Mon, 21 Oct 2019 12:36:52 GMT

You are right. It's just a name since I cannot share any of my real data. I did change it to the right one. It is "accepted" right before the {"<480"} part.. and yes all 3 parts was changed to the correct name 🙂

Also tried to create a new with the actual name "Test" (just to be sure) = still the same red lines.

However - it is the name (or label if you will) of the expression, and not a table field name. Do not know if that changes anything.

This it what It looks like in the expression:

Would think it to be some syntax-thing. 🤔

Re: Mark only highest value under a limit - background color expression

sunny_talwar — Mon, 21 Oct 2019 12:42:09 GMT

Yes, it def. does. You cannot use expression labels... you need to use the fields created in the script to do this.

Re: Mark only highest value under a limit - background color expression

Zwax — Mon, 21 Oct 2019 13:16:13 GMT

oh ok - sorry I didn't specify it.

Just to understand this:

This is only for the more advanced stuff, where you often use set analysis - right?

Easy stuff like: If(Test<='148',LightBlue(),) seems to works fine.

Re: Mark only highest value under a limit - background color expression

sunny_talwar — Mon, 21 Oct 2019 13:27:54 GMT

This should, but this will highlight all Tests under 148....

Re: Mark only highest value under a limit - background color expression

Brett_Bleess — Wed, 30 Oct 2019 15:16:29 GMT

Line, did Sunny's post help you get things working as you wanted? If so, please be sure to use the accept as Solution button on the post(s) that helped, so he gets credit and others know what worked. If you are still working upon things, here is a Design Blog post that may be of some use, actually a couple of them:

https://community.qlik.com/t5/Qlik-Design-Blog/Colors-in-charts/ba-p/1475280

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

The only other thing that will likely help is if you can work up an app with dummy data etc. so folks can see exactly what you have going on, that should help a great deal.

Regards,
Brett

Re: Mark only highest value under a limit - background color expression

Zwax — Thu, 31 Oct 2019 14:29:28 GMT

Sadly not. I haven't had much time to look at it, but I haven't given up yet.

Sure. Have added a quick sample. I would like to highlight only the highest number under the limit, and not all the values under:

Re: Mark only highest value under a limit - background color expression

sunny_talwar — Thu, 31 Oct 2019 14:42:45 GMT

Try this

If([Sum_Amount] = Max(TOTAL Aggr(Sum({<EVENT = {"=Sum(AMOUNT) < vLimit"}>}AMOUNT), EVENT)), Yellow())