The 'THEN' keyword cannot be used in this context.

[Nick Napier]

I'd like to have a property control to toggle between different y-axis functions. The expression

 

WeightedAverage([Weighting Factor],Min([NUMERIC_1]) over (Intersect([CAT_1],[CAT_2],[DATE_1])))

THEN Avg([Value]) OVER (LastPeriods(10,[Axis.X]))

THEN If(Count() OVER (LastPeriods(10,[Axis.X]))=10,[Value],null)

 

works correctly when I right-click the y-axis and enter it as a custom expression. However, when I enter it in the property control, I get the error "The 'THEN' keyword cannot be used in this context." How can I fix this I've attached photos and a .dxp file. Thanks!

[Gaia Paolini]

Hi

Thanks for uploading the example. I think your version does not work because when you edit the custom expression directly on the axis, the column [Axis.X] can be interpreted. When you create it as a property control, it is out of context, as you see [Axis.X] is red in your formula. It must be interpreted by a different mechanism.

I have an idea to work around it, see attached dxp. It is created with Spotfire 7.14, so in case you cannot open it I am adding the explanation. I noticed that setting Period=1 is equivalent to not smoothing, so:

I created an integerproperty control called SmoothingPeriod. In the drop-down it is assigned to 1 (Non-smoothed) and 10 (Smoothed).

The formula on the Y axis is now:

WeightedAverage([Weighting Factor],Min([NUMERIC_1]) over (Intersect([CAT_1],[CAT_2],[DATE_1])))

THEN Avg([Value]) OVER (LastPeriods(${SmoothingPeriod},[Axis.X]))

THEN If(Count() OVER (LastPeriods(${SmoothingPeriod},[Axis.X]))=${SmoothingPeriod},[Value],null) as [smoothed over ${SmoothingPeriod}]

So every reference to Axis.X is already in the custom expression.

Gaia