0 Replies Latest reply: Mar 21, 2017 7:43 PM by Dan Swinden RSS

    Convert Blank value to either null or 0

    Dan Swinden

      I will start by saying I am unable to impact how our data is loaded into our app.  I am an end user looking to modify an expression so that if a value doesn't exist, it shows as a 0.

       

      When i pull an account list using the below expression for a given SKU, there are accounts missing and it only shows me accounts that have ordered the SKU or previously ordered the SKU

       

      I am trying to pull volume of an item as a 1 or 0 (listed/not listed) for a rolling 3 month period.  If the customer has EVER ordered the item, just not in the period i am looking at, it returns a null, and therefore converts to a 0.  However, if the customer has never ordered the item in the sales history we have, then the value is neither a null or a 0, and therefore returns nothing.

       

      I want Null or blank to return 0 (in excel i would do an iferror(formula,0)).  Ideas would be welcomed!

       

      if(

       

       

      isnull(Sum({$<$(IGN_TIME_RTE),mo_rtime_typ={'R3M'}, [Brewer Type Code]={'L'}>}[Commercial Sales hL])),0,

       

       

      if(Sum({$<$(IGN_TIME_RTE),mo_rtime_typ={'R3M'}, [Brewer Type Code]={'L'}>}[Commercial Sales hL])

       

       

      >0,1,0))