Skip to main content
D
OneStream Employee
DRiderOneStream Employee
OneStream Employee
June 10, 2024

Additional math on GetDataCell expressions

  • June 10, 2024
  • 4 replies
  • 0 views

I have a column performing the following expression: GetDataCell(Divide(CVC(CYAct)-CVC(PYAct)), CVC(PYAct))):Name(PY % Var)

The column uses the following formatting: ExcelNumberFormat = 0.0%, ExcelUseScale = True, NumberFormat = 0.0, Scale = 0, ShowPercentSign = True

The Cell Amount of the column/row intersection is -26.956

 

When I run this cube view in OneStream, the number populates as -27.0%, which is okay with me. However, when I export it to Excel, the number populates as -2695.6%. 

My resolution is to multiply the number by 100 and then apply a scale to make the number appear correctly. However, if I multiply the division calculation above, it ignores the multiplication aspect. 

My multiplication formula is: GetDataCell((Divide(CVC(CYAct)-CVC(PYAct)), CVC(PYAct)))*100):Name(PY % Var)

Any suggestions on how to add this multiplication to my calculation, or another way for the number to appear properly within Excel?

4 replies

A
OneStream Employee
aformentiOneStream Employee
OneStream Employee
June 10, 2024

HI DRider ,

Have you tried using the Scale formatting option? since you have ExcelUseScale=TRUE, setting the right scale might do the trick instead of having to Multiply the GetDataCell() result. 

D
OneStream Employee
DRiderOneStream EmployeeAuthor
OneStream Employee
June 10, 2024

Thanks for the reply. Unfortunately scale won't work because I need the number to become larger. 

My scale is set to 0 right now (to override other scale settings in the cube view), and that is the only way for the proper number to appear in the OneStream view. If I change the scale, it won't appear correctly within the OneStream view. 

If I could apply a scale to the Excel view and no scale to the OneStream view, that would accomplish the objective, but I don't believe it's possible.

K
OneStream Employee
KarlTOneStream Employee
OneStream Employee
June 10, 2024

Have you tried setting the ExcelUseScale to false for that row/column to see if that solves it?

    R
    rstaana
    Newcomer
    June 10, 2024

    You can also use Col/Row Expressions under Sa mples in the Member Filter Builder: 

    rstaana_0-1718038487886.png

     

    D
    db_pdx
    Contributor
    June 10, 2024

    Hi DDrider: two things stand out to me:

    1) Your GetDataCell has unbalanced parenthesis, so its unclear if it is calculating correctly. Corrected would be:

    GetDataCell(Divide((CVC(CYAct)-CVC(PYAct)), CVC(PYAct))):Name(PY % Var)

    2) You're mixing formats. NumberFormat of [0.0%] and [0.0] are not the same. ShowPercentSign just slaps a % symbol at the end of the value; its a bad design from my perspective. If you are generating real decimal values (which is what you're striving for), I'd recommend the following:

    Scale=0, ExcelUseScale = True, NumberFormat = [#,###,0.0%], ExcelNumberFormat = [#,###,0.0%]

    Lastly, when in doubt, put the numbers into Excel and validate what the figure should be.

    Cheers,   -db

    Edit: typo

    H
    OneStream Employee
    HenningOneStream Employee
    OneStream Employee
    June 11, 2024

    Hi, are you using the same user culture in OneStream and in Excel? Seems like in OS, the "." is used as a decimal point, whereas Excel interprets it as a thousand separator (as is common e.g. in Europe).

    You could just add a linked CV which is formatted correctly to be opened in Excel instead of the original one such as "VC_001_ExcelOpen" in case neither of the above suggestions work out for you.

    Terms & ConditionsAccessibility statement