Qlik Community

Qlik Sense Cloud Discussions

Not applicable

Convert Blank value to either null or 0

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))

Community Browser