Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Did you mean:
Contributor II

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

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 ?

Regards,

Buchannagari.

Labels (2)

• ### Visualization

2 Replies
MVP

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).

Partner - Creator III

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 '|');

Source:
;
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])
,	 To as To_tmp
Resident Bucket
;
// Joining the key so we can use the description as dimension
left join (Source)
,	 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.