Skip to main content
D
DRider
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
aformenti
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
DRiderAuthor
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
KarlT
June 10, 2024

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

R
rstaana
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
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
Henning
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