CODEWITHSUNDEEP
spotfire
Ignus
Ignus

Reputation: 139

Column to rows and highlight difference between values in the same group

I have a huge table with data structured like this:

Originale table data

And I would like to display them in Spotfire Analyst 7.11 as follows:

Desired Visualizazion

Basically I need to display the columns that contain "ANTE" below the others in order to make a comparison. Values that have variations for the same ID must be highlighted.

I also have the fields "START_DATE_ANTE" and "END_DATE_ANTE" which have been omitted in the example image.

Upvotes: 1

Views: 151

Answers (1)

Chelsea
Chelsea

Reputation: 511

Amusingly, if you were limited to just what the title asks, this would be a very simple answer.

If you wanted this in a table where the rows are displayed as usual, and the cells are highlighted, you can do this by going to properties, adding a newGrouping where you select VAL_1 and VAL_1_ANTE and add a Rule, Rule type "Boolean expression", where the value is:

[VAL_1] - [VAL_1_ANTE] <> 0

This will highlight the affected cells, which you can place next to each other. You can even throw in a calculated column showing the difference between the two columns, and slap it on right next to it. This gives you the further option to filter down to only showing rows with discrepancies, or sorting by these values.

However, if you actually need it to display the POSTs on different lines from the ANTEs, as formatted above, things get a little tricky.

My personal preference would be to pivot (split/union/etc) the data before pulling it in to Spotfire, with an indicator flag on "is this different", yes/no. However, I know a lot of Spotfire users either aren't using a database or don't have leeway to perform the SQL themselves.

In fact, if you try to do it in Spotfire using custom expressions alone, it becomes so tricky, I'm not sure how to answer it right off. I'm inclined to think you should be able to do it in a cross table, using Subsets, but I haven't figured out a way to identify which subset you're in while inside the custom expressions.

Other options include generating a table using IronPython, if you're up to that.

Upvotes: 1

Related Questions