Perform normalization in line charts with line-by function with respect to a particular value in X-Axis.

[Sudhansu Mishra]

Hi all,

I'm trying to normalize the line chart with respect to a particular Y-value corresponding to one predefined value of X-axis. Could someone indicate how to go ahead and do this in Spotfire. I've reviewed some links like https://www.tibco.com/blog/2017/02/06/how-to-normalize-or-standardize-yo... but they don't describe how to normalize as per my requirement.

For eg. I'd like to normalize each line with respect to their corresponding Y-value when X=6000.0

[James Watts]

I would do something like the following:

Sum([Y]) / Sum(If([X]=6000,[Y],0)) over (All([X]))

The If statement returns the value of [Y] when [X]=6000 - by using the OVER (ALL) clause it returns this for all rows.

[Sudhansu Mishra]

Thanks @jim_w. Two questions

 

1. if [x]=6000, ,[y],0 . Does this mean that if x is not equal to 6000, a value of 0 is returned else the value of Y at x=6000 is returned  If this is the case, then the division will return an infinite value for all the X values except 6000.

 

2. I'm interested in the true value instead of Sum. Is it possible to use Value instead of Sum or any reason why Sum is used. 

 

Reason is when I used the connotation /statement provided, I didnot get any output and the error is The expression is not valid. 

[James Watts]

1. No - the 'sum over all' means the values of

If([x]=6000,[y],0)

are summed for all values of [X]. As these will be zero for all values that do not equal 6000, and [Y] at 6000, the value returned will be [Y] at [X]=6000.

2. I used Sum because an aggregation method is required in the chart when dividing a function by an OVER function.

I've attached a DXP with an example of this.

[James Watts]

1. if [x]=6000, ,[y],0 returns zero apart from when [X]=6000.  These values are then summed OVER ALL([X]) - so the result for all values of [X] is the value of [Y] when [X]=6000.

 

2. Sum([Y]) is needed because an aggregated value is required for the chart.  As long as your values of [X] are unique it should not be an issue.  If you use calculated columns rather than a custom calc in the chart, you do not need the Sum().

[Sudhansu Mishra]

Thanks @jim_w for the example. 

 

Understood and this works for me now. I was trying to enclose everything before OVER function using parenthesis and it was the reason for the error.