Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paul_ripley
Creator III
Creator III

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

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],

paul_ripley_0-1636390193280.png

 

Does anyone know how I could so this?

Thanks

Paul

 

Labels (1)
1 Solution

Accepted Solutions
Or
MVP
MVP

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

View solution in original post

6 Replies
abhijitnalekar
Specialist II
Specialist II

Hi @paul_ripley 

Please use amend your field in the script as below

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

 

 

Regards,
Abhijit
keep Qliking...
Help users find answers! Don't forget to mark a solution that worked for you!
paul_ripley
Creator III
Creator III
Author

Hi Abhijit

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

paul_ripley_0-1636555506108.png

 

Or
MVP
MVP

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
Creator III
Creator III
Author

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

Paul

Or
MVP
MVP

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
Creator III
Creator III
Author

Great many thanks