Pick and Match function for intervals

[KevSan]

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.

[Almen]

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

[KevSan]

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

[Almen]

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.

[marcus_sommer]

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