topic Change Background color on Rank in QlikView

Change Background color on Rank

stabben23 — Fri, 20 Dec 2013 07:05:10 GMT

Hi,

is it possible to change the background color that depends on the Rank Value.

In my example I have 9 account and I want to show them in separate textobject. The textbox included '1050' should in this case be green. Is it possible for the textobject to know about the dimension outside the object it self? The expression is sum({$<Account={'50'}>}Value)

//Staffan

Re: Change Background color on Rank

rohit214 — Fri, 20 Dec 2013 07:23:09 GMT

in text box>>properties >> colur>>caculate>> make expression

if(Account='50',Red())

thanks

rohit

Re: Change Background color on Rank

stabben23 — Fri, 20 Dec 2013 07:34:11 GMT

Hi Rohit,

It has to be dynamic, tomorrow account 40 is highest.

//Staffan

Re: Re: Change Background color on Rank

tresB — Fri, 20 Dec 2013 07:48:29 GMT

Is it that, you want to max value dynamically. PFA

Expression, something like (in text box):

=FirstSortedValue(Aggr(Sum(Amt),Months), -Aggr(Sum(Amt), Months))

Re: Change Background color on Rank

stabben23 — Fri, 20 Dec 2013 07:48:59 GMT

if(Rank(aggr(sum(Value),Account))=1,lightGreen(),lightred()) is my calculated backgroundcolor that dont works right now.

Re: Change Background color on Rank

stabben23 — Fri, 20 Dec 2013 08:00:35 GMT

Hi tresesco,

Not exact, I will get green in all the boxes with this expression

if(FirstSortedValue(Aggr(Sum(Value),Account), -Aggr(Sum(Value), Account)),Green(),red())

That is because this is the highest value in "just this textbox" it has to compare "outside the box" with all other Account values. Because all Account will be shown in there own textobject, and the highest sum(Value) should be green.

//Staffan

Re: Re: Change Background color on Rank

tresB — Fri, 20 Dec 2013 08:22:44 GMT

Well, so your text boxes are specific to acounts.

Then try like:

If( Aggr(If(Account='50', Rank(Sum(Value))), Months) =1, lightgreen(), red())

If this doesn't work, please share your sample app.

Re: Change Background color on Rank

stabben23 — Fri, 20 Dec 2013 08:38:15 GMT

Yes they are specific to Account, and I can not harcode 50 in my expression, because tomorrow it could be another Account with the highest Value. You can see on top in this discussion how it should be, this is just an example.

I havent "draw" all textboxes but have in mind that you have a Textobject for each Account. And the Account with the highest Value should be "Highligted" Green as you se in the table.

//staffan

Re: Change Background color on Rank

rohit214 — Fri, 20 Dec 2013 09:10:04 GMT

Hi ,

PFa

Hope it helps.

Thanks

Rohit

Re: Re: Change Background color on Rank

tresB — Fri, 20 Dec 2013 09:16:01 GMT

Then how do you decide on number of text boxes? Is it something like: first text box for Rank 1, then second textbox for rank -2 ?
Please attach a sample app explaining the exact output.

Re: Change Background color on Rank

stabben23 — Fri, 20 Dec 2013 09:30:05 GMT

Re: Change Background color on Rank

stabben23 — Fri, 20 Dec 2013 09:30:46 GMT

No, I have 10 texobject.

Re: Change Background color on Rank

stabben23 — Fri, 20 Dec 2013 10:09:26 GMT

One of this textbox shall be green, the other ones red.

Re: Re: Change Background color on Rank

tresB — Fri, 20 Dec 2013 10:21:00 GMT

Staffan,

I have not got yet the logic of yours; how do you get the values account wise in text boxes if it is not hardcoded?

Re: Change Background color on Rank

stabben23 — Fri, 20 Dec 2013 10:28:16 GMT

Hi tresesco,

You are right about that, the values is hardcoded, but that is not the case here. I have

sum({$< Account ={'10'}>}Value) for ex in the expression for textbox with value from Account 10.

The case here is to get the textbox with the highest value to change color. And I try to do that with backgroudcolor.

Re: Re: Change Background color on Rank

tresB — Fri, 20 Dec 2013 10:37:25 GMT

This?

=If( Aggr(Rank(Sum(Value)), Months) =1, lightgreen(), red())

Re: Change Background color on Rank

stabben23 — Fri, 20 Dec 2013 10:47:12 GMT

No, works in tables but not in the textobjects, all went red.

Re: Re: Re: Change Background color on Rank

stabben23 — Fri, 20 Dec 2013 10:50:11 GMT

Here is the file tresesco

Re: Re: Re: Re: Change Background color on Rank

tresB — Fri, 20 Dec 2013 11:09:28 GMT

PFA

Re: Re: Re: Change Background color on Rank

stabben23 — Fri, 20 Dec 2013 11:18:43 GMT

tresesco, this is absolutly a possible solution and maybe the best. I dident "se" that I have to hardcode the Account inside the textobjects backgroundcolor. Do you think there is a way to NOT use the Account No in the expression?

Thanks!

//Staffan