Projected Margin % using Global variables & UDDC

Modified on Tue, 5 Mar at 6:58 PM

Projected Margin % using Global variables & UDDC

Usage of global variable in custom measures saves users from the tedious task of modifying various expressions and filter formula manually and provides simple “what if” analysis scenarios. Since the Global variables are created at the dataset level, they can be accessed globally with various expressions.


Note: Global variables created for one dataset cannot be accessed from within objects created from another dataset.

 

Example 1:

Let's see how to calculate the Projected Margin based on the Cost of goods percentage change.

The below Margin cross-tab shows the Gross sales, Cost of Goods and Margin percentage ( a UDDC derived as “((GrossSales-CostofGoods)/GrossSales)*100 ” ) of various product categories for the year 2014.

image001.png

SALES MARGIN CROSS-TAB


Now, to create Projected Margin based on Cost of goods, you need to create a global variable 'COGS_Perc_Change', and use it in expression for creating UDDC for Projected Margin.

Step 1

 

In the Cross-tab Toolbar, click Manage global variablesThe system displays the Manage global variables dialog box.

 

Step 2

 

In the Manage global variables dialog box, click the Add icon. The system displays the Add Global Variable dialog box.


image002.png

ADD GLOBAL VARIABLE DIALOG

Step 3

 

In the Name field, enter the name as 'COGS_Perc_Change'.

 

Step 4

 

Since the variable values are not associated with any dimension, you do not have to check the checkbox  Associate with dimension.

Enter the value as '5' in the default value text-box and click OK to save the variable.

 

Once Global variable is created, you need to create a Custom Measure Column (UDDC) 'Projected Margin'. This UDDC should be calculated on the basis of the variable COGS_Perc_Change, Cost of Goods and Gross Sales columns.

Step 5

In the Sales cross-tab, right-click on the measure cell. The system displays the menu.

Step 6

 

In the menu, select Add Column. The system displays Add custom measure (UDDC) dialog box.

image003.png


ADD CUSTOM MEASURE (UDDC) DIALOG

Step 7

Enter the name as 'ProjectedMargin' and create the expression in the Expression box as '((GrossSales-(CostofGoods*(1+($COGS_Perc_Change$/100))))/GrossSales)*100 '. Global variable can be used with $ sign within UDDC expression.

 

Step 8

 

The VERIFY EXPRESSION will verify the expression and display a message if the expression is valid or not. Click OK.

 

The sales margin cross-tab will be displayed with the Projected Margin values. The Projected Margin values will be based on the global variable value. Default value of global variable is 5, so expression will be - (((GrossSales-(CostofGoods*(1+(5/100))))/GrossSales)*100).


image004.png

SALES MARGIN CROSS-TAB WITH PROJECTED MARGIN COLUMN

You can change the value of COGS_Perc_Change to see different projections of Margin. Any change in COGS_Perc_Change would be reflected in all cross-tab where the value of COGS_Perc_Change is used.

 

The list of global variables created through Manage global variables can be accessed from the Global

variables option in the Cross-tab toolbar.

 

Below given are some scenarios of various projections of the Margin when there is a change in the Cost of Goods percentage.

 

Scenario 1: When there is a discount of 3% applied to the Cost of Goods.


image005.png

GLOBAL VARIABLES DIALOG FOR CHANGING THE VALUE


image006.png


PROJECTED MARGIN AFTER APPLYING THE DISCOUNT



Scenario 2: When there is an increase of 10% in the Cost of Goods.



image007.png



GLOBAL VARIABLE WINDOW FOR CHANGING THE VALUE



image008.png



PROJECTED MARGIN AFTER INCREASING THE COST





Note: This article is based on Smarten Version 5.x. This may or may not be relevant to the Smarten version you may be using.
custom measure Global variable User Defined Data Column UDDC what if scenarios Manage global variables Expression Business Intelligence Support Business Intelligence Articles Business Intelligence Training

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article