Forum Discussion

CCI01's avatar
CCI01
New Contributor
4 years ago

Variance Analysis in Cube View

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        

 

Cube View

4 Replies

Sort By
  • Mustafa_A's avatar
    Mustafa_A
    Contributor II
    4 years ago

    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 - 

     

    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: 

     

     

     

     

  • Koemets's avatar
    Koemets
    Contributor
    4 years ago

    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's avatar
    NicoleBruno
    Valued Contributor
    4 years ago

    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's avatar
    aricgresko
    Contributor III
    4 years ago

    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.