Additional math on GetDataCell expressions

[DDrider]

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?

[aformenti]

HI @DDrider ,

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. 

[DDrider]

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.

[KarlT]

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

[DDrider]

If set to false, it defaults to scale = 0 and doesn't change since scale is set to 0 in the column formatting.. 

[aformenti]

Looking at the GetDataCell expression, is this accurate? Looks like you have a misplace braket:

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

alternatively, you could try multiplying the numerator instead:

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

[DDrider]

Thank you, unfortunately either calculation still ignores the *100. May not be possible to apply  multiplication to CVMath. 

[rstaana]

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

[db_pdx]

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

[Henning]

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.