Cube View math operation error
I have a cube view where some of the mathematical operations in the last two columns on the right ( Difference and Percent Difference) are not working. The formula that I have in these last columns is as follows:
GetDataCell(BWDiff(CVC(OP) ,CVC(Actual))):Name(Difference)
GetDataCell((CVC(Difference) / CVC(OP)*100)):Name(Percent Difference)
The yellow highlighted cells in the right hand columns are the ones where the math is not working and they are not populating with results.
Below is the structure of the cube view I have:
I have noticed that the cells on the right columns where the math is not working and not populating are the ones where the corresponding row has accounts that are in the form of a sum of two or more accounts, as an example for my Row1 I have:
GetDataCell("A#60100+A#60900"):Name(Salaries & Benefits)
It is apparent that the issue in the cells that are not populating is due to the accounts in those rows being the sum of two or more accounts as opposed to a specific single account (as the math for those is working)
How do I go about resolving this issue?
Thank you for your time and participation in advance.
Hi FM: this is a common issue where you have two GetDataCell calcs that will have a conflicting intersection. To have the report displayed correctly you'll need to tell OneStream to do a Row Override (or column override depending on the situation).
From the Difference column of the report layout, select Row Overrides. Specify the row names in the section called Row Range (Row1, Row5) and repeat your formula in the Member Filter: GetDataCell(BWDiff(CVC(OP) ,CVC(Actual)))
The above is the easiest way to solve this reporting issue, but are other potential ways to solve this:
Create a real hierarchy parent for those combination of accounts, use the real member in the cube view selection
Create DynamicCalc accounts that performs the math in the dynamic account, use this dynamic account member in the cube view selection
Create DynamicCalc U8 members that perform the scenario comparison math
Lastly, if you want to learn more I remember there is an Academy Training on Cube View Overrides that might be helpful to review.
Cheers,
-db
