Question using Switch() in email template

[Jessy_Mrozowski]

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.

[Kirk_Masden]

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.

[Jessy_Mrozowski]

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

[Kirk_Masden]

I’m not confident in my ability to answer this question. I hope @Aleksi will have time to weigh in later.

[Aleksi]

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?

[Jessy_Mrozowski]

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.

[Jessy_Mrozowski]

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.

[Aleksi · Accepted 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.

[Jessy_Mrozowski]

that could work, just have it in a holder table.

Thanks Aleksi

[Aleksi]

You’re welcome