Forum Discussion

DDrider's avatar
DDrider
New Contributor III
7 months ago

Additional math on GetDataCell expressions

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?

Cube View
  • aformenti's avatar
    aformenti
    Contributor II
    7 months ago

    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's avatar
      DDrider
      New Contributor III
      7 months ago

      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's avatar
        KarlT
        Contributor III
        7 months ago

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

  • rstaana's avatar
    rstaana
    New Contributor
    7 months ago

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

     

  • db_pdx's avatar
    db_pdx
    Valued Contributor
    7 months ago

    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's avatar
    Henning
    Valued Contributor II
    7 months ago

    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.