Variance Analysis in Cube View

CCI01
New Contributor

I'm working on a cube view that does a variance analysis between 2 difference scenarios. The scenarios will always vary based on the scenario member selected by the user.

Some of the rows use the formula "GetDataCell" or a dynamic calc in the member filter. I've found that if I use GetDataCell then Col 4 does not calculate the variance correctly. I started using dynamic calcs to work around this issue. Unfortunately, I have also experienced this issue using dynamic calcs as well.

Has anyone else had this issue and what were some of the things they did to resolve this?

 

My columns are setup like so:

Col 1                     Col 2                   Col 3                           Col 4                          

Scenario#1          Scenario#2        Col 1 - Col 2               Col 3/ Col 2        

 

4 REPLIES 4

Mustafa_A
Contributor II

Hi @CCI01 

I've worked on a few VAR analysis reports - might have some ideas we can try.

Can you share a small snippet of your report? Just wanna see the rows & columns.

 

Solution1:

Have you tried using this as your member filter?  Set you dimension to 'scenario' and insert this expression. 

You can replace S#Scenario with your scenario parameter that you built. 

GetDataCell(S#Scenario1-S#Scenario2):Name(Difference)

and, create a new member line, add all accounts underneath it.

For Example - 

Mustafa_A_0-1646449834157.png

 

Solution2:

I see your Col4 is calculate by dividing col3/col2. Please confirm

So maybe try --

GetDataCell(Divide(CVC(Col3Name),CVC(Col2Name)))

CVC for columns, and CVR for rows.

Example: 

Mustafa_A_1-1646450786898.png

 

 

 

 

Koemets
Contributor

Ok, in light of the KISS concept, that we all should be painfully familiar with, may I suggest Col4 formula as:

DIVIDE(col 1-col 2,col 2)

NicoleBruno
Contributor III

Hello! 

Yes, there's an issue that you can't use formulas in both rows and columns. Per OS support from an old ticket I submitted: 

You are still not able to use a GetDataCell on both a row and column in either a quick view or a cube view.
Your best option is to create a cube view (which can be opened in Excel) and use row or column overrides to get at what you need. Or you can use cube view math to get your variance.

 

 

aricgresko
Contributor III

I have a column set that is doing essentially the same thing. Here's how I accomplished what you're asking for.

My columns:

Col_Month                             Col_MonthComp                        Col_Var           %_Var%

Select Scenario/Month 1       Select Scenario/Month 2           Variance $       Variance %

 

The third column (Col_Var) uses BWDiff to calculate the variance based on the account type.

Here's my member filter:  GetDataCell(BWDiff(CVC(Col_Month),CVC(Col_MonthComp))):Name(Var $)

 

The fourth column (%_Var%) then divides to calculate the variance percentage

Here's my member filter:  GetDataCell(Divide(CVC(Col_Var),CVC(Col_MonthComp))):Name(Var %) 

 

For your GetDataCell issue, you'll need to use column &/or row overrides to have the variances columns calculate properly.  I'd try to avoid using the GetDataCells in the rows, by using alternate hierarchies where possible.