Substracting Dynamically Previous Row Values in CrossTab

[Vincent Thuilot]

Hello,

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?

[Gaia Paolini]

can you share an example and the axes you used in the cross table?

[Vincent Thuilot]

Hello Gaia,

Thanks for your reply.

sure please find attached an example of what I've tried so far.

I am using Spotfire 11.4.

Ideally the calculation would be dynamic and would calculate the difference from the previous row, no matter the sorting or the filtering.

Thanks in advance for your support!

Vincent

[Gaia Paolini]

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.

[Vincent Thuilot]

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.

 

[Vincent Thuilot]

Also, at times we don't want to compare all companies altogether, but just 2.

in this case the result in the cross-table could be even misleading

 

Would there be a way to solve this? maybe with a Python script or something?

[Vincent Thuilot]

FYI I could figure it out with Tableau (working dynamically) but I would definitely prefer to continue with Spotfire here

[Gaia Paolini]

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?

[Vincent Thuilot]

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

[Gaia Paolini]

ok but what is the definition of current and previous? There are no dates and no clear order, apart from the year.

[Vincent Thuilot]

sorry, by current I meant a given row: for a given row's weighted price avg, i want to compare in a 2nd column the difference between this given row and the one just before. And this dynamically (sorting/filtering),

Like in the below (except as you said earlier that it works only on a static base):

 

[Gaia Paolini]

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)

Vertical:

<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]

[Gaia Paolini]

From the Tableau picture you shared, the data does not actually look sorted by the average price

[Vincent Thuilot]

Please see attached.

[Vincent Thuilot]

Would there be a way to replicate from the above video?

[Gaia Paolini]

it would take an Iron Python script. I am having a look

[Gaia Paolini]

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

[Vincent Thuilot]

Thanks a lot Gaia for this :), I will spend some time to implement it and check out how it could work for my case.

Keep you posted

[Vincent Thuilot]

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 :)