Spotfire cross table - calculate difference when multiple hierarchies are on Columns

Question

I am trying to create a custom expression in Spotfire to calculate the difference between time periods in a cross table which has multiple hierarchies on the columns (for example, one is a product hierarchy and the other is the time hierarchy). All the examples I have seen seem to work only if the time hierarchy is the single property selected on an axis.

My scenario is that I want to show in a cross table the sum of sales per category or products (selected through a product hierarchy slider on the columns axis) across different time periods (selected through a time hierarchy also on the column axis), together with the difference from the previous period for the same category or product. The rows axis is set to the customer name.

The default expression Spotfire suggests for "Difference" actually shows the difference relative to the previous "cell" in the cross table, regardless if it is for the same product/category or not. In the example screenshot here, the difference for Product A for 2014 is shown as "13" even though there is no previous time period for product A. The value of "13" is actually the difference between the sales for product A for 2014 and the sales for product B in 2014 from another category, which happens to be the previous value on the same row.

The example analysis can be found at here.

I would much appreciate some hints on how I can get the difference to the previous time period for the same product and/or category (depending on what the user selected in the product hierarchy slider).

Answer 1

I managed to build an expression to calculate properly the difference I wanted to have. The expression is:

Sum([Amount]) - Sum([Amount]) OVER (Intersect(PreviousPeriod([Axis.Columns]),[Hierarchy.ProductHierarchy]))

Basically, this is intersecting the previous period on the columns with the non-time hierarchy, which seems to yield the proper result.