Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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).
Hi @paul_ripley
Please use amend your field in the script as below
Coalesce([Charge Amount],0) as [Charge Amount]
Hi Abhijit
Not sure why but the script brings back errors when i add this in
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?
When you say use a measure what do you mean? Trying to add the rows up but not all rows contain data
Paul
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).
Great many thanks