06-10-2024 10:51 AM
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?
06-10-2024 10:59 AM
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.
06-10-2024 11:03 AM
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.
06-10-2024 11:27 AM
Have you tried setting the ExcelUseScale to false for that row/column to see if that solves it?
06-10-2024 12:53 PM
If set to false, it defaults to scale = 0 and doesn't change since scale is set to 0 in the column formatting..
06-10-2024 11:41 AM
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)
06-10-2024 12:54 PM
Thank you, unfortunately either calculation still ignores the *100. May not be possible to apply multiplication to CVMath.
06-10-2024 12:55 PM
You can also use Col/Row Expressions under Sa mples in the Member Filter Builder:
06-10-2024 06:41 PM - edited 06-10-2024 06:43 PM
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
06-11-2024 02:54 AM
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.