Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for
Did you mean:
Contributor II

## Pick and Match function for intervals

Hi everyone,

I would like to use the pick and match functions to color background of a field but I do not understand on how to use it. Can someone help me?

So the idea, is to calculate the difference between 2 durations and if result is :

< 20 min -> Green

>= 20 min <  30 min -> Orange

>= 30 min -> Red

Can you help me please ?

Have a nice day.

Labels (1)
• ### Visualization

4 Replies
Creator II

I don't know your datafields, but you can use If to pick the color for background.

``````If(Field or Measure < 20, rgb(165,211,0),

IF(Field or Measure >= 20 min and Field or Measur e<30, rgb(250,186,35),

IF(Field or Measure >= 30, rgb(230,74,60))))
``````

Contributor II
Author

Ok for nested if but is there a way to use pick and match ? Just for my understanding? 😄

Creator II

Sure, it is possible but needs extra work.

Just for clarification, pick() chooses an expression of a list based upon the ordinal number given by either a field or another function. pick(n, expr1,expr2,exprN)

Match() returns the location of the searched expression inside the given list. match(Str, expr1,expr2,exprN)

In Code:

``````pick(
match(Field, 'Red','Orange','Green'),
rgb(#,#,#),
rgb(#,#,#),
rgb(#,#,#)
)``````

So you have to define a field within your script because match needs a string to compare the list against.

But the moment you define that in the script, you might as well store the rgb(#,#,#) in the field and thus just have to use the field without any pick(match()) at all.

Pick(match()) could be used for such scenarios, for example with:

pick(match(-1, Duration<20, Duration<30, -1),
color1, color2, color3)

but logically it's the same like the nested if-loops - just using a different syntax which provides mostly a better readability.

Different to it would be if you could apply a logic like:

pick(ceil(Duration / 10), color1, color2, color3)

The second approach based on logic to calculate the pick-index directly. If and how such logic is possible depends on the amount of possible return-values and if the intervals are homogeneous or not - whereby the index-calculation could be of course more complex by using some rangemin/rangemax to fetch the lower/upper outliers, more and other rounding and/or including also some if-branches.

Further it's possible to create an appropriate matching-table within any other tool and loading it into the data-model and a pick(match()) fetched these data within adhoc-variables.

The both last methods could become quite complex but are very helpful if the performance with the simple methods are too slow or if it's just too tedious to create a whole bunch of branches for it.

- Marcus