null values

[#1]

good afternoon, this is me having the following situation, I have one expression that consists of 2 parts subtraction is applied

part 1)

Sum({<[%Cuenta contable]={$(=$(vCuentaIngresoGAS))},[Clasificacion categoría]={'Sector Residencial'}>}

if (IsNull([Consumo del cliente])=-1, 0, [Consumo del cliente] ))

-

Sum({<Año={$(= Max(Año))},Mes={$(= Month(AddMonths(Max(Fecha),-1)))},[%Cuenta contable]={$(=$(vCuentaIngresoGAS))},[Clasificacion categoría]={'Sector Residencial'}>}

if(IsNull([Consumo del cliente])=-1,0,[Consumo del cliente]))

minus

part 2)

(

Sum( {<FlagGT = {1}, FuenteInfo = {'Costo Regulado-->Excell'},[%Cuenta contable]={$(=$(vCuentaCostoGAS))},[Categoría]={'Domestico'}>}

if (IsNull(Mts3DOMESTICO_Costo_reg)=-1,0,Mts3DOMESTICO_Costo_reg))

-

Sum( {<Año={$(= Max(Año))},Mes={$(= Month(AddMonths(Max(Fecha),-1)))},FlagGT = {1}, FuenteInfo = {'Costo Regulado-->Excell'},[%Cuenta contable]={$(=$(vCuentaCostoGAS))},[Categoría]={'Domestico'}>}

if (IsNull(Mts3DOMESTICO_Costo_reg)=-1,0,Mts3DOMESTICO_Costo_reg))

)

)

I have not loaded data for part 2, but I try to check if the value is null I replace it by zero, apparently did not make it because part 2 throws me null value and this makes the entire expression is part 1 minus the part 2 me throw nil. As I can do to correct this?, I appreciate your suggestions: for example:

Part 1 result 50, Part 2 result null, then part 1 result minus part 2 resul is equal null.

[Peter_Cammaert · Accepted solution]

Surround all SUM() expressions with a  RangeSum() function. RangeSum will treat all non-numerical values as 0. E.g.

=RangeSum(sum(Part1A), -Sum(Part1B), -Sum(Part2A), Sum(Part2B))

Peter

[#3]

Peter excellent, thanks it worked