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?

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.

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.