Calculate difference between columns

Jennifer_Moser
5 yrs ago
5replies
Answered

I'm trying to create a very basic grid that has two Terms in the columns and several measures in the rows. How can I add another column that shows the difference between the measures for the terms? In other words, I'd like to add a 'Difference' column that shows the difference of 54 applicants, 56 admitted, and 33 enrolled.

Thanks,
Jen

- Dvir_Buzaglo
- 5 yrs ago
- Reported - view
Hi Jennifer,

You can do it simply by selecting the two years from the grid itself (in the calculation order with the Ctrl button), then right-clicking them, and selecting the "Subtraction function".

Hope it helps.
- Jennifer_Moser
- 5 yrs ago
- Reported - view
Thank you!! Is it possible to do this if the columns are named sets? For example, we have one named set that is equal to the Current Registration Term, and another that is equal to the Prior Year Registration Term. When I try to do this subtraction calculation, the column is blank. It works fine when I pick specific dimension values.

Thanks again,

Jen
- Jennifer_Moser
- 5 yrs ago
- Reported - view
Dvir Buzaglo Also, is it possible to add the up/down arrow indicator to ONLY the Difference column? When I drag my measure to the Indicator area, it applies it to all columns.

Thanks,

Jen
- Dvir_Buzaglo
- 5 yrs ago
- Reported - view
HI Jennifer,

You would need to create two formulas out of the Two named sets and in "Script mode" add the ".Item(0)" to get the first and only member results, for example:

After creating the two formulas, go to the grid and select the two calc and create a subtraction calculation (as we have seen in last correspondence)

mark the calculation as shareable, in the grid, select it along with the named set.

Regarding your second question about indicators, currently, they are only available for KPI and measures and not for calculated members (from dimensions),

You can set the calculation background/Foreground color for negative or positive numbers by editing the calculation foreground color properties.

You should open the calculation and copy the syntax in script mode
Exit it without saving and reopen the calculation again,

Paste the syntax to the foreground color property of the calculation with the next IF statement.
IIF( 'SYNTAX'<0,"selected color","selected color")

After saving go to the grid and select the calculation along with the named sets, in query tab select the "Model format"

Hope it helps.
- Chris.1
- 4 yrs ago
- Reported - view
Thank you Dvir Buzaglo