Jump to content
We've recently updated our Privacy Statement, available here. ×

Substracting Dynamically Previous Row Values in CrossTab

Vincent Thuilot
Go to solution Solved by Gaia Paolini,

Recommended Posts


I have a Crosstab where I calculate WeightedAvg of a column, based on another one.

Weighted Avg calculation works fine for every row.

Now I need to add another column that is comparing one line's Weighted Avg with the previous one's Weighted Avg. A basic substraction, but nothing seems to work!

I tried it like this:

WeightedAverage([volume],[price]) as [Weighted Avg], WeightedAverage([volume],[price])- WeightedAverage([volume],[price]) OVER (Previous([Axis.Rows])) as [Difference]

So far it calculates correctly differences but not for every row.

Also, if I apply filters, it currently does not calculate the difference, even though the Weighted Avg is still correctly calculated for every row.

Any thoughts?

Link to comment
Share on other sites

The formula works, in the sense that the previous value in the axis is the original one, before sorting. So if I remove the sorting with respect to the average, I get this, which appears correct.

However, the order in which the companies appear in the cross table is rather arbitrary, as they are sorted in string order, so Company10 is before Company3. I am not sure what you want to compare against, but if you have a different order in mind, maybe you can define some additional column to set the desired order, or set a custom sort order for the Company column.

Also note that Previous will return nothing if the previous row is filtered out.


Link to comment
Share on other sites

Hello Gaia,

Thanks for taking the time to respond.

My end-users actually need to use the filters that are on top (to deep-dive into specific regions, specific products).

What we want to compare against: simply the difference of avg price from most expensive till lowest expensive. the absolute value is a good indicator for pricing positioning here.

Unfortunately when doing so, the table is not updating accordingly.


Link to comment
Share on other sites

so to recap:

  • you want to calculate the weighted average of the Volume (weighted with Price)
  • you are calculating this weighted average with respect to each Company
  • you want to be able to filter by Product/Company/Year/Region
  • in the end you want to compare the sizes of the weighted averages

Is my understanding correct?

Link to comment
Share on other sites

yes mostly:

  • It is the avg Price weighted by Volume actually.
  • I am calculating this weighted average with respect to each Company
  • I most definitely want to be able to filter by Product/Company/Year/Region
  • Very specifically to my end-users: they would like to compare in a 2nd column the difference between the current row's price weighted avg with the previous row's weighted price average.

Hope it's clear

Link to comment
Share on other sites

  • Solution

It can be done in Spotfire if you add a row definition that creates a sort order that reflects the values of the weighted average.

Note that this means the sorting is fixed.

Horizontal: (Column Names)


<DenseRank(WeightedAverage([Volume],[Price]) OVER ([Company]),"desc") as [Rank] NEST [Company]>

note that in Settings you need to check the Current Filtering only option.

Cell values:

WeightedAverage([Volume],[Price]) as [Avg Price], WeightedAverage([Volume],[Price]) THEN [Value] - First([Value]) OVER (Previous([Axis.Rows])) as [Difference]

Link to comment
Share on other sites

Use the following script to change the sorting and the direction of the sorting.

The script dynamically changes the way the Rank column is calculated.

The input parameters are:

  • vis (of type Visualization): the specific cross table you want to act on.
  • sort_order (of type String): can be asc or desc.
  • rank_by (of type String): can be Average or Company.
# Copyright © 2023. TIBCO Software Inc. Licensed under TIBCO BSD-style license.from Spotfire.Dxp.Application.Visuals import *from Spotfire.Dxp.Data import * ctable=vis.As[CrossTablePlot]() exp1='<DenseRank(WeightedAverage([Volume],[Price]) OVER ([Company]),"{}") as [Rank] NEST [Company]>'.format(sort_order)exp2='<DenseRank([Company],"{}") as [Rank] NEST [Company]>'.format(sort_order)if rank_by=='Average': ctable.RowAxis.Expression=exp1else: ctable.RowAxis.Expression=exp2
Link to comment
Share on other sites

Hi Gaia,

So after trying a few things, it seems that adding the ranking (and also the year in my case) into the cross-table made it possible to make the right calculations of avg price per company at yearly level.

The hardest thing to figure out was actually this piece in the column/row Settings that you mentioned about checking the Current Filtering only option. It's indeed not obvious from the start and I tend to forget it because i'm not using it so often. It's something that one should keep in mind when working with cross-tabs.

Again, many thanks for the support and the detailed answer, it made my day :)

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...