Finding blank cells in a number line item

IanHall2000

I want to filter by a number line item to show just blank cells, but I cannot figure out how. I know the ISBLANK function is what I want to use, but it cannot be used on numbers. Is there a way to find blanks in a number formatted line item similar to the ISBLANK function?

Accepted answers

bhatjaved

@IanHall2000 Number formatted line item has got 0 in each cell by default and you may use another line item to flag 0 cells and consider that as a blank cell, but keep in mind that 0 is also a number when it comes to calculation.

All comments

bhatjaved

Alexandru.Rosca

Hi @IanHall2000 , why would you need to use another line item for creating a filter? Will you use it further on as reference in other formulas or is just for display purpose in a dashboard. If so, have you tried to use the native filtering option in Anaplan? You can one of two: line item is blank or line item = 0.

Additionally, you could use line item = 0. As I'm aware of, numbered formatted line items will display a 0 by default, so you will get the desired result. Bare in mind to use a Boolean formatted line item for your filter.

Saket2022

Not sure if it is possible to have a blank cell in a number formatted line item.

However, you may try to nest the number in a TEXT function. For example - ISBLANK(TEXT(cell)) .

Ivan_Lo_

Hi Ian,

It sounds like your number line item display blank when it is zero. I think you need to change the formula to check if line item = 0.

Cheers

alexpavel

@IanHall2000 as also others mentioned: for numeric formatted line-items it's not possible to have BLANK values in Anaplan. The default value for numeric line-items is zero.

If you have blank values in source files, when they are imported into Anaplan, they will be automatically transformed into zero values.

Hope it helps

Alex

IanHall2000

I didn't realize that missing data would show up 0 for numbers so this is helpful, but it brought up another concern. I'm working with data that has both 0s that are actually 0 and other 0s that are defaulting to 0 because there was no data for that cell. The problem is that I want to differentiate between what is a true zero and what is a missing value for other calculations. Is there any way to set up another line item that can help distinguish between the two?

ryan_kohn

What would be the logic you would use to determine if a value is a true zero or an empty value? If you can determine the logic, you can most likely codify that in the model.

One example I can think of is if these values are coming through an integration:

1. Import the values as text, which allows BLANK as a value, and will also contain zeroes for true zeros.

2. Have a Boolean flag that identifies if the value is BLANK.

3. Convert the text to a number using VALUE().

4. In the UI, you can use DCA based on the Boolean in (2) to show only true zeros, and hide the blanks.

Ultimately, you would just need to figure out your own method to generate that Boolean for true blanks vs empty values. Just keep in mind that we generally want to avoid using Text as a format.

Quick Links

Can you help?

Unanswered questions

Automating Flat File Uploads to Anaplan Data Orchestrator (ADO) – Best Practices and Methods
Hello Anaplan Community, I am seeking guidance on the most effective ways to automatically upload flat files to Anaplan Data Orchestrator (ADO). Currently, our process involves loading flat files to an Anaplan Datahub using Anaplan APIs and custom Python scripting. With Anaplan Data Orchestrator's capabilities for data…
How to Design Simulation Planning in Anaplan with Multi-Level User Input and Step-by-Step Logging?
Hi Anaplan Community, I'm working on a simulation planning model where users need the flexibility to input pricing adjustments at any level of the product hierarchy, and I’d love your guidance on best practices. 📌 Scenario Overview: We have a 4-level product hierarchy: Brand Category Sub Category SKU (lowest level) The…
OAUTH 2.0 vs Google Sheets Extension
Hi, My org is looking to explore leveraging Google Apps script to extract data out of Anaplan and update already built templates in Google Sheets. We use the Anaplan Extension currently but find it crashes often with the amount of tabs, data, etc. Has anyone used both Anaplan OAUTH 2.0 API and Goolge Apps Script to write…