2 Replies Latest reply on Jul 19, 2018 9:13 AM by Alan Perez

    Sum of Null Values

    ben.sachs

      Hello Community,

       

      I've looked through many of the forum posts and I'm not able to find a solution for this, so I'm creating a new conversation.

       

      I'm trying to get the total number or sum of rows from a count of items. Unfortunately, I'm not able to upload my workbook because it contains sensitive information, but below are some screen shots of what I'm trying to accomplish.

      • I have a list of providers that have sent us order requests
      • The order requests are defined as specific type of modality or "product" (modality in the image below can be thought of as our products)
      • I have created a product count which allows me to see the number of products each provider has ordered
      • There are some customers that may only order certain products each year/qrt/month etc.
      • I'm trying to find the providers that have not ordered a product for the year/qrt etc.
      • The challenge is that the data for the products do not have a numerical identifier, and instead are only in string format.
      • In my list I'm able to see that some of the customers have null values for either 2017 or 2018. I am able to sort so the null values are descending, but I need to filter so that only the null values for the year are appearing.
      • As you can see in the image below for the year 2017 I have a number of null values, except I'm not able to filter so that only null values appear. I'm also unable to sort so that null values are at the top of my 2017 column, rather than always appearing at the bottom.

       

      Note:

      I've tried creating measures from "ISNULL", "IFNULL", ZN and even other calculation on combined measures/dimensions.

      I've also tried using the conditions for "Only Null Values", But my sheet doesn't show anything because it filters all of the data off of the sheet.

       

      Basically, I'm trying to find

      1- The total number of providers that didn't order anything for date

      2- Filter the date so that only the null values appear and any providers that have ordered do not appear.

       

      Any help/suggestions is appreciated.

       

      Null_Providers.png