Spotfire - Dividing one row value with another in a cross table

Question

I have a cross table which shows fruits sold across months as shown below:

Now, I would like to see a table (shown below) which divides one cell with another for a particular column:

Please let me know if this is possible with an OVER statement.

---

UPDATE:

@Chris - I have transformed my original table in access and could create the below cross table visualization in Spotfire.

Note: Apples sold, Oranges sold, watermelons sold are now separate columns after applying transformation.

Cross table visualization

However, I would like to see the cross table in the format below.

Transposed table

Please let me know if this is possible. If this could be achieved with Iron Python script, I am fine with that.

Thank you!

Answer 1

As your data is in a tall skinny format, my original answer didn't work. I've kept the answer as part of this in case it helps someone else, see below.

With your update, you seem to have managed to create a crosstable with the data you want. While it would be helpful if you could show your exact axis expression on row-, column- and measure axis, I can try to illustrate how to transpose the data in the cross table as you want it to. See the last last section for an explanation.

With a tall skinny format, you will not be able to use an OVER expression as you are looking to do explicit comparisons (at least I don't think it's possible). First I'd like to note that you have to decide somehow which fruits you want to compare to each other, or you might want to compare all of them. The exact solution would depend on what your real use case is and how dynamic you need it to be.

Possible solution:

In your crosstable, define your row axis as (column names):

<[Axis.Default.Names]>

Put your time axis on the column axis, for instance:

<BinByDateTime([Time],"Year.Month",1)>

On your measure axis, you will now add all the comparisons you are interested in, such as:

Sum(if([Fruit]="Apple", [Sold],null)) / Sum(if([Fruit]="Watermelon",[Sold],null)) as [Apples vs Watermelons],
Sum(if([Fruit]="Orange",[Sold],null)) / Sum(if([Fruit]="Watermelon",[Sold],null)) as [Oranges vs Watermelons]

Here's a link to how this looks with my own very small dummy dataset:

It's possible you can combine this with user controls in a text area, and maybe the $map() expression function to make it more dynamic if needed. If you want a lot of dynamic behavior in this you might need to change to use a datafunction instead.

---

Old answer

Below is my original answer, that includes presumption about the data that didn't apply in this case. Keeping it here if anyone else actually have this situation.

In table 1 you have two categorical axes, one for time and one for fruit. And the measure you are showing is the quantity of sold fruits.

In table 2 I'd say that you only have one categorical axis, time. After that, you have two measured values, and your row axis would be '(Column Names)'.

The measures would be the count of apples divided by the count of oranges, as well as the count of oranges divided by the count of watermelons. If you'd right click the measure axis and go to custom expression you would have something like:

Sum([Apples]) / Sum([Oranges]) as [Apples vs. Oranges], 
Sum([Oranges]) / Sum([Watermelons]) as [Oranges vs. Watermelons]

Also, go to Properties -> Formatting and set your percentage formatting to your liking.

Transpose in cross table

It's a bit hard to understand from your screenshots, so I'll guess what your axis expressions are. Even if I guess wrong, the same approach is applicable regardless initial state. You just need to swap the row axis with the column axis. You can do it by using the axis selectors, or copy and paste from the custom expression editor. If this is the kind of configuration you have from the start:

Then by swapping the axis expressions this is what you get: