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, "-".

Accepted answers

All comments

rob_marshall

@n0200309

Have you tried using the Round function?

Link to Round

gaurav_sharma-bng

@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!

Lori001

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.

GingerAnderson

@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

rob_marshall

@n0200309

In the blueprint, how many decimals are defined?

Lori001

@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

rob_marshall

@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.

Quick Links

Can you help?

Unanswered questions

How to use Client ID and Client Secret for API request directly?
Hi all, The Client ID and Client Secret are available, intended for uploading and downloading files. Could anyone advise how to use these credentials DIRECTLY to make API requests? Thank you!
12_ [COMPLETE GUIDE] Uphold Support Number
At Uphold customer support ⭐+1-888-355-2348 accounts may be temporarily disabled or restricted for various reasons. Raise a concern on priority basis at Uphold support ⭐+1-888-355-2348 if you notice any suspected malicious activity, or a problem during account recovery. To contact Uphold support directly at…
New UX Filtering for Hierarchy sibling
I have A6 as the parent list and two sibling lists: A6.5 and A7. The A7 list includes an attribute that maps to A6.5. In the UX, the user will select A6.5 using a content selector, and the grid (dimensioned by A7) should display only the A7 items associated with that selected A6.5. Since the selector is a page context and…