Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
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
Champion
Champion

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

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

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