Hey Sagar, let's break down why your expression is throwing an error and get it working smoothly!

The Core Problem #

First off, the THEN keyword in your code is the main culprit. In Spotfire's expression language, THEN only works as part of a CASE WHEN conditional structure—you can't use it like natural language to mean "then do this next." That's what's causing the syntax break.

Looking at your expression, it seems you're trying to combine two distinct calculations:

1. A base metric: Sum([Exp Increase]) / Sum([History]) * Sum([Prices])
2. A rolling 4-period weighted value: Sum([Value]) OVER (LastPeriods(4,[Axis.Columns])) / Sum([Prices]) OVER (LastPeriods(4,[Axis.Columns]))

Corrected Expression Options #

Here are two fixes based on what you're actually trying to achieve:

Option 1: Combine the Two Calculations Directly

If you want to perform an operation between the two results (like adding or multiplying them), remove the THEN and use a clear arithmetic operator. For example:

(Sum([Exp Increase]) / Sum([History]) * Sum([Prices])) + (Sum([Value]) OVER (LastPeriods(4,[Axis.Columns])) / Sum([Prices]) OVER (LastPeriods(4,[Axis.Columns])))

Swap the + with *, -, or / depending on your specific business logic.

Option 2: Use a Valid CASE WHEN Conditional

If you intended to switch between the two calculations based on a condition (the only valid use for THEN), you need to complete the CASE WHEN structure. For example:

CASE
  WHEN [YourConditionColumn] = "TargetValue"  -- Replace with your actual condition column and value
  THEN Sum([Exp Increase]) / Sum([History]) * Sum([Prices])
  ELSE Sum([Value]) OVER (LastPeriods(4,[Axis.Columns])) / Sum([Prices]) OVER (LastPeriods(4,[Axis.Columns]))
END

Bonus: Simplify the Rolling Period Calculation #

You can clean up the rolling 4-period part since both sums use the same window—Spotfire will calculate the window once for both:

Sum([Value]) / Sum([Prices]) OVER (LastPeriods(4,[Axis.Columns]))

This gives the exact same result as your original rolling calculation but is much easier to read.

Quick Checks to Avoid Future Issues #

• Double-check that all column names (like [Exp Increase] or [History]) match exactly what's in your dataset—typos are a super common mistake!
• Verify that [Axis.Columns] refers to the correct dimension in your visualization (e.g., a date or period column that defines your rolling window).

内容的提问来源于stack exchange，提问作者sagar joshi