Product: TIBCO Spotfire®
How to combine certain column values (High, Medium) to display as an additional level (High, Medium , High & Medium)
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..
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:
Very Low & Low
High and Low
High & Medium
Also check the LevelAdditionDXP.dxp (see attached) for more information.