03-04-2022 11:27 AM - last edited on 05-24-2023 01:02 PM by JackLacava
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
03-04-2022 10:29 PM
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:
03-07-2022 02:57 PM
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)
03-07-2022 03:25 PM
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.
03-08-2022 12:04 PM - edited 03-08-2022 12:08 PM
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.