In the last article, I had talked about dissecting Paint Repository. Let me now give you some insights on how to use the Calculation Wizard to create new columns that compare two existing columns.
In the Paint repository, we have two columns "Dollars" and "Month Ago Dollars". Using the calculation wizard, we can create the following four calculated columns based on the expression templates provided by OBIEE
CurrentX - ComparisonX
1.0 * CurrentX / ComparisonX
- Percent Change
100.0 * (CurrentX - ComparisonX) / ComparisonX
100.0 * (CurrentX / ComparisonX)
CurrentX -> Column using which Calculation wizard was invoked
ComparisonX -> Column chosen in the wizard for comparison
Here, if we want to calculate "% Chg Month Ago Dollars" i.e change in Dollars from its Month Ago value, we can use the calculation wizard as shown below.
Right click "Dollars" column and select "Calculation Wizard"
Click Next and choose "Month Ago Dollars" as shown below
Click Next and choose the comparison column that we would like to create. We can choose all four and the wizard will create 4 new columns with the code in it to do the comparison.
Click Next and Finish. A new column called "% Chg Month Ago Dollars" will be created as we chose only Percent Change above. See the code that the calculation wizard has created for the new calculated column.