Spotfire cross table - calculate difference when multiple columns are in columns
I am trying to create a custom expression in Spotfire to calculate the difference between time periods in a crosstab that has multiple hierarchies in columns (for example, one is a product hierarchy and the other is a temporary hierarchy). All examples I've seen seem to only work if the time hierarchy is the only property selected on the axis.
My scenario is that I want to show in a crosstab the sales amount for each category or products (selected using the product hierarchy slider on the column axis) in different time periods (selected by the time hierarchy also along the column axis), along with the difference with a previous period f or the same category or product . The line axis is specified by the customer name.
By default the Spotfire expression suggesting "Difference" actually shows the difference from the previous "cell" in the crosstab , whether it is in the same product / category or not . In the example screenshot below , the difference in Product A for 2014 is shown as "13" although for Product A. The value "13" is actually the difference between sales for Product A in 2014 and sales for Product B in 2014 from a different category , which is the previous value on the same line.
A sample analysis can be found in here .
I would really appreciate if I can get the difference in the previous time period for the same product and / or category (depending on which user selected in the product hierarchy slider).
source to share
I managed to create an expression to correctly calculate the difference I wanted to have. Expression:
Sum([Amount]) - Sum([Amount]) OVER (Intersect(PreviousPeriod([Axis.Columns]),[Hierarchy.ProductHierarchy]))
Basically, this intersects the previous period in non-urgent hierarchy columns, which seems to give the correct result.
source to share