Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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))))
Ok for nested if but is there a way to use pick and match ? Just for my understanding? 😄
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