topic Re: How to sum a dimension where some of the entries are null in QlikView

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

paul_ripley — Mon, 08 Nov 2021 16:51:27 GMT

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

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

abhijitnalekar — Tue, 09 Nov 2021 03:16:59 GMT

Hi @paul_ripley

Please use amend your field in the script as below

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

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

paul_ripley — Wed, 10 Nov 2021 14:45:50 GMT

Hi Abhijit

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

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

Or — Wed, 10 Nov 2021 14:58:19 GMT

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?

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

paul_ripley — Wed, 10 Nov 2021 15:28:48 GMT

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

Paul

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

Or — Wed, 10 Nov 2021 15:35:34 GMT

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

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

paul_ripley — Wed, 10 Nov 2021 15:42:47 GMT

Great many thanks