How to combine certain column values (High, Medium) to display as an additional level (High, Medium , High & Medium)

How to combine certain column values (High, Medium) to display as an additional level (High, Medium , High & Medium)

Details

Suppose we have a column in a table which stores four levels such as "High", "Medium", "Low" and "Very Low". Now in a cross table we can show these Levels and a metric such as "Dollar" value. There may be a business requirement to see additional levels such as "High & Medium", "Medium & Low", "Low & Very Low" in addition to the existing levels. The Cross Table will show all these levels and the "Dollar" value for each level. This can be achieved in Spotfire by using steps provided in the Resolution section below..

Resolution

First create some calculated columns as shown below and then add a new data table from the existing data table and use transformation "Unpivot".

A). Insert > Calculated Columns like the following.

1). High = If([level]="High",[value]) 2). Low = If([level]="Low",[value]) 3). Medium = If([level]="Medium",[value]) 4). Very Low = If([level]="Very Low",[value]) 5). Very Low & Low = If(([level]="Very Low") or ([level]="Low"),[value]) 6). High & Medium = If(([level]="High") or ([level]="Medium"),[value]) 7). Medium & Low = If(([level]="Low") or ([level]="Medium"),[value]) ADD MORE COLUMN AS PER YOUR REQUIREMENT

B). Next add a data table using the existing data table and use transformation : Unpivot Transformation name: Unpivot Columns to pass through: (None) Columns to transform: High Low Medium Very Low Very Low & Low High and Low High & Medium

Also check the LevelAdditionDXP.dxp (see attached) for more information.