topic Re: BG Color in QlikView

BG Color

subbareddykm — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

i m trying to achieve for color coding for my expression using my three fields value, i tried a lot but no luck.

can any one tell me how to achieve this.

Note : In Green, Yellow and Red those values may change in future. i want to make it dynamic.

Thankq in adv.

Re: BG Color

Anil_Babu_Samineni — Tue, 23 May 2017 04:11:46 GMT

Check this, Change accordingly as per your need

Re: BG Color

subbareddykm — Tue, 23 May 2017 04:19:53 GMT

this is not correct anil,

in my requirement each KPI is having difference range and that range is also not fixed it may change in future.

Re: BG Color

Anil_Babu_Samineni — Tue, 23 May 2017 04:21:38 GMT

Then you must talk about Business and requirement. You are releasing any thing to do. If you release few key words which is relevant to the Business then we may help you for sure

Re: BG Color

Anil_Babu_Samineni — Tue, 23 May 2017 04:25:45 GMT

May be this when you talk about Dynamic. Even i am not sure whether you are expecting same. But check may be helps you

Re: BG Color

subbareddykm — Tue, 23 May 2017 04:42:57 GMT

As per my requirement My output should be like below:

Re: BG Color

subbareddykm — Tue, 23 May 2017 04:46:55 GMT

Here you have create some variables and same value you are passing to all KPI's but If you check my data you see Each KPI Color range is different and dont not hard code those values.

Instead of entering hard code values can we get those value from Green , Yellow red for each KPI wise.?

Re: BG Color

Anil_Babu_Samineni — Tue, 23 May 2017 04:53:37 GMT

Ok, We can achieve same but do you have only 22 KPI's or mostly these KPI's or constant or it's changes when filter Happens. But, I wonder with out Hard code for Green, Yellow, red fields how can we achieve. Let me think play around and please confirm whether KPI are constant or not, If it constant i may help you (P.S - Constant means only 21 KPI's are there or it may increased?)

Re: BG Color

subbareddykm — Tue, 23 May 2017 05:05:28 GMT

Its constant

Re: BG Color

Anil_Babu_Samineni — Tue, 23 May 2017 05:18:40 GMT

Then try like below for BG expression

=If(KPI='KPI1' and [Sum(Week_Actuals)] < 0,If(Green > '>=90%', Green(), If(Yellow >= '>=70%' and Yellow <= '<=90%', Yellow(), If(Red < '<70%', Red()))),

If(KPI='KPI2',If(Green > '>=98%', Green(), If(Yellow < '<98%', Yellow(), If(Red < '<95%', Red()))),

If(KPI='KPI3',If(Green > '>87.5%', Green(), If(Yellow >= '>=77.5%', Yellow(), If(Red < '<77.5%', Red()))),

If(KPI='KPI4',If(Green > '>86%', Green(), If(Yellow > '>84.3%', Yellow(), If(Red > '<84.3%', Red()))

)))))

=If(KPI='KPI1' and [Sum(Week_Actuals)] < 0,If(Green = '>=90%', Green(), If(Yellow = '>=70%' and Yellow = '<=90%', Yellow(), If(Red = '<70%', Red()))),

If(KPI='KPI2',If(Green = '>=98%', Green(), If(Yellow = '<98%', Yellow(), If(Red = '<95%', Red()))),

If(KPI='KPI3',If(Green = '>87.5%', Green(), If(Yellow = '>=77.5%', Yellow(), If(Red = '<77.5%', Red()))),

If(KPI='KPI4',If(Green = '>86%', Green(), If(Yellow = '>84.3%', Yellow(), If(Red = '<84.3%', Red()))

)))))

And similar to till KPI22. There must be good approach as i know this one is simply when constant for the object. May be marcowedel‌ cleveranjos‌ marcus_sommer cal help us

Re: BG Color

nagarjuna_kotha — Tue, 23 May 2017 07:23:17 GMT

Hi,

I worked with your data and found some work around it.

check it out whether it matches your requirement. !

Script:

Final:

LOAD *,

if(Green_Flag=0,Right(Green_Range,2),Green_Range) as G,

if(Yellow_Flag=0,Right(Yellow_Range,2),Yellow_Range) as Y,

if(Red_Flag=0,Right(Red_Range,2),Red_Range) as R;

LOAD*,

If(WildMatch(Green_Range,'*.*'),1,0) as Green_Flag,

If(WildMatch(Yellow_Range,'*.*'),1,0) as Yellow_Flag,

If(WildMatch(Red_Range,'*.*'),1,0) as Red_Flag;

Data:

LOAD KPI,

[Metric Unit],

Goal,

Green,

If(Len(KeepChar(Green,'.0123456789'))>3,Left(KeepChar(Green,'.0123456789'),4),KeepChar(Green,'.0123456789')) as Green_Range,

If(Len(KeepChar(Yellow,'.0123456789'))>3,Left(KeepChar(Yellow,'.0123456789'),4),KeepChar(Yellow,'.0123456789')) as Yellow_Range,

If(Len(KeepChar(Red,'.0123456789'))>3,Left(KeepChar(Red,'.0123456789'),4),KeepChar(Red,'.0123456789')) as Red_Range,

Yellow,

Red,

[Sum(Week_Actuals)] as Val

FROM

(biff, embedded labels, table is Sheet1$);

-Nagarjun