null values

Tue, 04 Feb 2014 13:04:30 GMT

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.

Re: null values

Peter_Cammaert — Tue, 04 Feb 2014 13:13:29 GMT

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

Re: null values

Tue, 04 Feb 2014 14:01:30 GMT

Peter excellent, thanks it worked

topic null values in QlikView

null values

Re: null values

Re: null values