Iโm trying to figure out a way to determine if a โNumberโ is between two values. To calculate this number I use avg(select()) to filter out for dates and specific users so itโs a very lengthy equation to have multiple times in IFS() statements.
Is there a way to store the calculation in a variable or use it in a Switch() statement?
Switch(โNumberโ, 2 < 5, a, 5 < 8, b, 8 < 10, c, 10 <, d).
Hope that makes sense, cheers.
Solved! Go to Solution.
How about a normal column? You could first calculate a value into a normal column and then trigger the workflow action. Then it would not affect your daily basis.
Hi! I donโt think that your switch statement will work as you have written it. As I understand it (and I hope someone will correct me if I have this wrong) SWITCH() expressions involve matching and do not allow you to ask if something is greater than something.
Iโm guessing that IFS() would be better choice in this situation. Perhaps something like
ifs([Number]<5,โBelow rangeโ,and([Number]>=5,[Number]<=10),โIn rangeโ,[Number]>10,โAbove rangeโ)
Apologies in advance if I have misunderstood your question.
Thatโs what I was afraid was the case, itโs unfortunate we cannot store temporary variables for reuse as would clean up the code a lot as opposed to having
<< average( select(Payouts[Hourly Yards Cleaned], and( [Scooper] = [_THISROW].[ID], [Date] > today()-7, [Date] <= today() ), false ) ) >>
written 15 times for a quick calculation
example:
<<Double Avrg = average( select(Payouts[Hourly Yards Cleaned], and( [Scooper] = [_THISROW].[ID], [Date] > today()-7, [Date] <= today() ), false ) ) >> <<IFS( Avrg <= 2.5, "Z", and(Avrg > 2.5, Avrg < 5), "Y", Avrg >= 5, "X")>>
Iโm not confident in my ability to answer this question. I hope @Aleksi will have time to weigh in later.
As @Kirk_Masden mentioned, yes thatโs trueโฆ you canโt use the average like a variable. Are you able to calculate the average with the virtual column so then you would not need to calculate it like 15 times?
I wouldnโt want to do that as it would be a waste of resources/slow down app for something that isnโt needed on a day-to-day basis. Also Iโm calculating the average of several โPayoutsโ from X period to Y period, so donโt think that would be an option. Perhaps itโs something to look into for the future? Being able to hold temporary variables? It would make writing Templates much easier as sometimes with the Tables and complex calculations it can start to look really messy and difficult to find errors.
none-the-less, it looks like for the time being iโll have to stick with copy/pasting the expression multiple times inside of IFS() statement.
How about a normal column? You could first calculate a value into a normal column and then trigger the workflow action. Then it would not affect your daily basis.
that could work, just have it in a holder table.
Thanks Aleksi
Youโre welcome
User | Count |
---|---|
43 | |
26 | |
24 | |
14 | |
12 |