How to get the Dynamic colors in Pivot table by using pick and match functions

[Buchannagari]

Hi,

i am using below nested if condition to show colors

If( sum([Males Planted]) <= 100, RGB(115, 150, 255),

If( sum([Males Planted]) >100 and sum([Males Planted]) <=1000,RGB(235, 235, 224),

If( sum([Males Planted])>1000 and sum([Males Planted]) <=2000,RGB(217, 65, 103), RGB(255, 191, 0) )

)

)

Is possible to get the same colors by using pick and match functions ?

If yes please help me here

Regards,

Buchannagari.

[Or]

You probably could if you wanted to using a different if() structure or mathematical functions, but I don't really see any advantage to doing that.

Note that your conditions are more complex than necessary - for each condition except the first you can remove the first sum part, since by definition each brand would not be entered unless the previous branch was evaluated to false (so if we reach the second branch, our sum will always be bigger than 100 and there's no need to check that again).

[eddie_wagt]

Hello @Buchannagari ,

I would strongly suggest to make this statement more easy and more dynamic by calculating the buckets in the loadscript and adding the buckets as a dimension table where you could define these colours.

// Setting up the buckets
Bucket:
LOAD * INLINE [Key, From, To
1, 0,100
2, 101,1000
3, 1001, 2000
4, 2001, 99999999999]
;

// Setting up the bucket description
Bucket_Dim:
LOAD * INLINE [Key| Description| Color
1| 0-100| RGB(115, 150, 255)
2| 100-1000| RGB(235, 235, 224)
3| 1001-2000| RGB(217, 65, 103)
4| >=2000|RGB(255, 191, 0) 
] (delimiter is '|');

// Loading the source
Source:
LOAD Field,[Males Planted]
;
Source:
LOAD * Inline [Field, Males Planted
1, 300
2, 400
3, 1000
4, 2345
5, 100
6, 50
7, 1020
8, 2999
];

// Matching the Males Planted with the buckettable
left join (Source)
IntervalMatch([Males Planted])
LOAD From as From_tmp
,	 To as To_tmp
Resident Bucket
;
// Joining the key so we can use the description as dimension
left join (Source)
LOAD From as From_tmp 
,	 To  as To_tmp
,	 Key
Resident Bucket
;
drop fields From_tmp
,			To_tmp
from Source;
;

Kind regards

Eddie

If this answers your question or solves your issue, be sure to mark the answer as correct by clicking 'Accept as Solution'. This will mark the post as solved and other Qlikkies will gravitate towards this post as it as a possible solution for their issue. Multiple responses can be accepted as a solution so make sure to select all that apply.