Anaplan calculates 0.000000000000000111022 value but it should be zero.

Can someone help me or point me to if there are already discussion around this topic?

In Anaplan, I am doing a moving sum prior 11 months and it looks like it returns a 0. As seen in the screenshot for Sep 23. But when I copy and paste the value into Excel, it is showing 0.000000000000000111022.

Copied SEPT 23 into in Excel show a nonzero value:

What is causing this and how can Fix this issue?

I would like to understand why first.

I think I can simply fix it by adding to the formula, highlighted in yellow in screenshot below, but I would like to understand why it is not calculating correctly a zero, when it looks like a zero, "-".

Answers

  • @n0200309

    Have you tried using the Round function?

    Link to Round

  • @n0200309 - Just to provide an explanation on why, I believe this is a data issue. I would like you to clear all values from the POL_CNT line item by making the formula =0

    Once all values are zeroed out, then you can re-input the valid values, like 1 for Mar and Apr and -1 for Jul and Sep - and hopefully, you will then see a 0 in both Anaplan and Excel. Let me know if this works!

  • rob_marshall, the value should not be rounded though. Decimals are allowed.

    gaurav_sharma-bng, I zeroed out the POL_CNT line item by making the formula 0 and putting the formula back in. I also zero'd out the POL_CNT and reinput the values and SEP 23 is still showing "-" in Anaplan but when copied and paste into Excel, it is showing 0.000000000000000111022.

    Here is Anaplan:

    Here is copy and paste:

    Oct 22

    Nov 22

    Dec 22

    Jan 23

    Feb 23

    Mar 23

    Apr 23

    May 23

    Jun 23

    Jul 23

    Aug 23

    Sep 23

    POL_CNT

    0.0

    0.0

    0.0

    -0.41667

    0.0

    1.0

    1.0

    0.0

    0.0

    -1.0

    0.0

    -0.58333

    POL_CNT MOVINGSUM

    7.41667

    7.41667

    4.41667

    4.5

    3.5

    4.0

    3.41667

    -0.5833300000000001

    -1.25

    -1.4166699999999999

    -0.9166699999999999

    1.1102230246251565E-16

    As mentioned, I can add "IF MOVINGSUM(POL_CNT, -11, 0) < 0.01 AND MOVINGSUM(POL_CNT, -11, 0) > -0.01 THEN 0" but I just don't understand why Anaplan is not calculating it correctly.

  • @rob_marshall @gaurav_sharma-bng Tagging you for visibility - the tag in the comment above didn't work (the poster's reply to each of you).

    Thanks!

    Ginger

    Community Manager

  • @n0200309

    In the blueprint, how many decimals are defined?

  • IL2024
    edited October 2023

    @rob_marshall

    0 Decimal place.

    But even if I changed the decimal places, it still shows as "-" in Anaplan and copy and paste still shows the same:

    Oct 22

    Nov 22

    Dec 22

    Jan 23

    Feb 23

    Mar 23

    Apr 23

    May 23

    Jun 23

    Jul 23

    Aug 23

    Sep 23

    Oct 23

    Nov 23

    Dec 23

    POL_CNT

    0.0

    0.0

    0.0

    -0.41667

    0.0

    1.0

    1.0

    0.0

    0.0

    -1.0

    0.0

    -0.58333

    0.0

    0.0

    0.0

    POL_CNT MOVINGSUM

    7.41667

    7.41667

    4.41667

    4.5

    3.5

    4.0

    3.41667

    -0.5833300000000001

    -1.25

    -1.4166699999999999

    -0.9166699999999999

    1.1102230246251565E-16

    0.0

    0.0

    0.0

  • @n0200309

    Because it is not truncating the underlying data, it is a rendering mechanism to show, in your case, 0 decimal points which is the hyphen. This is why I mentioned to use the round function.