Adding to a IFS expression

[Scott_Hall]

I have this expression.

IFS(([Qty 1] < [Location].[Container Interval 1]), [Location].[Price 1], [Qty 1] >= [Location].[Container Interval 1b], [Location].[Price Interval 1b], [Qty 1] >= [Location].[Container Interval 1], [Location].[Price Interval 1])

Question: How would I add to this expression that if the value of the [Price Structure] column in the Location table is “Flat Rate” the result would equal blank/ empty.

For context the IFS expression calculates a tier price per container at different container quantity thresholds. However if the overall price plan is “Flat Rate” (instead of "per container) there is no need for the price calculation so the result just needs to be blank or empty.

Thank you in advance for any direction you can provide.

[Aleksi]

Add [Price Structure]=“Flat Rate,"", to the beginning after IFS(. If the value needs to be number, use NUMBER(TEXT(""))

[Scott_Hall]

@Aleksi

Clarification
[Rate Structure] is in the Location table so I de-referenced in the expression. I want it to produce the math expression result if the Rate Structure in the Location table is “per container”. Empty if “flat rate” so I used “per container”. Is that right?

So as I understand your reply, I tried this:

IFS(([Location].[Rate Structure] = “Per Container”,"", [Qty 1] < [Location].[Container Interval 1]), [Location].[Price 1], [Qty 1] >= [Location].[Container Interval 1b], [Location].[Price Interval 1b], [Qty 1] >= [Location].[Container Interval 1], [Location].[Price Interval 1])

Got this from expression builder:

Arithmetic expression ‘(([Location].[Rate Structure] = “Per Container”) < [Location].[Container Interval 1])’ does not have valid input types

Thoughts? And Thank You.

[Aleksi]

One pair of () too much. Try…
IFS([Location].[Rate Structure] = “Per Container”,"",[Qty 1] < [Location].[Container Interval 1], [Location].[Price 1], [Qty 1] >= [Location].[Container Interval 1b], [Location].[Price Interval 1b], [Qty 1] >= [Location].[Container Interval 1], [Location].[Price Interval 1])

[Scott_Hall · Accepted solution]

Yes Sir. That did it. I was wrong about the Rate Structure. It did need to be “Flat Rate” to accomplish the goal. So the final expression that worked is:

IFS([Location].[Rate Structure] = “Flat Rate”,"",[Qty 1] < [Location].[Container Interval 1], [Location].[Price 1], [Qty 1] >= [Location].[Container Interval 1b], [Location].[Price Interval 1b], [Qty 1] >= [Location].[Container Interval 1], [Location].[Price Interval 1])

Thank you @Aleksi for your help. Possibilities with Appsheet are exciting. Enjoy learning more as I go.

[Aleksi]

You’re welcome