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
-
0
-
@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!
0 -
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.
0 -
@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
1 -
0
-
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
0 -
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.
0
