How to sum a dimension where some of the entries are null

[paul_ripley]

Hello

I have a stable in QV and  the amounts are held in the dimension AM.Charge Amount .  I would like to sum the values in this dimension but some of the values are NULL.  I have tried amending the Load script but it still seem to pull in NULLS and so I cannot sum the column  - "Totals on First Row" doesnt work

IF(IsNull([Charge Amount])=0,[Charge Amount],'0') as [Charge Amount],

Does anyone know how I could so this?

Thanks

Paul

[abhijitnalekar]

Hi @paul_ripley 

Please use amend your field in the script as below

Coalesce([Charge Amount],0) as [Charge Amount]

[paul_ripley]

Hi Abhijit

Not sure why but  the script brings back errors when i add this in

[Or]

Coalesce is a newer function, only available in the 2021 release of QlikView. From a logical perspective, it's no different from:

if(isnull([Charge Amount]),0,[Charge Amount])

I'm a tad confused as to your goal and approach here - why are you trying to create a total for a dimension rather than using a measure? How do you expect it to aggregate a total when you aren't using an aggregation function? Why are you placing the string '0' instead of using a numeric 0?

[paul_ripley]

When you say use a measure what do you mean?  Trying to add the rows up but not all rows contain data

Paul

[Or · Accepted solution]

If you use a measure (expression), you should be able to simply sum([Charge Amount]). This will automatically ignore null values and the totals should work. Note that this requires using a straight table or pivot table (but ideally you want to do that anyway, Table objects aren't the best option in most cases).

[paul_ripley]

Great many thanks