Finding blank cells in a number line item

IanHall2000
IanHall2000
in Modeling

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?

Tagged:

Best Answer

  • bhatjaved
    bhatjaved
    Answer ✓

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

Answers

  • Alexandru.Rosca
    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
    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_
    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
    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
    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
    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.

Categories