10 Replies Latest reply: Mar 30, 2010 11:01 AM by Miguel Angel Baeyens de Arce

# problems with a count of nulls

hi ...

i have an expression on a pivot table

=Count (if (date>=dateFrom and date<=DateTo), Field) //where dateFrom and DateTo are variables and Date the name of another filed.

and the count give me always 0.

i think is because the Field has NULLs. How can i filter the expression to don´t count the nulls??

if anybody thinks that the problem is another i am open to all the answers...

Thanks

• ###### problems with a count of nulls

Hola,

Puedes comprobar fácilmente la existencia de valores nulos en el campo Fecha con

`If(Len(Fecha) = 0, 'Es Nulo')`
Por otro lado, comprueba que los valores del campo "Fecha" y los de las variables "FechaDesde" y "FechaHasta" tienen el mismo formato de fecha (ya que si no, la comparación siempre dará falso), si no lo tienen, siempre puedes forzar a comprobar con
`Date(CampoFecha) = Date(VariableFecha)`

Una sugerencia para incorporar ese análisis como análisis de conjuntos podría ser

`Count({< Fecha = {">=\$(vFechaDesde)<=\$(vFechaHasta)"} >} Field)`
donde campo y variables tienen el mismo formato de fecha. Aparte el rendimiento seguramente será mayor.

Saludos.

• ###### problems with a count of nulls

the last expression gives shows me the ( in red... but it doesn't says expression error..

and in the pivot table it doesn't show me any value

Thanks for all.

• ###### problems with a count of nulls

Where exactly does it show the error or the red parenthesis?

Do the variables exist and have formatted date as to match with Field?

You're bound to do, have you changed "Field" for your date field?

• ###### problems with a count of nulls

i have upload the image of the error...

thanks.

• ###### problems with a count of nulls

`=Count({< date = {">=\$(dateFrom)<=\$(DateTo)"} >} Field)`

I don't know spanish(?), but I think it was just an example, where you must change the variable names that you use in your report.

Andris

• ###### problems with a count of nulls

sorry in english

=count( {< Date = {">=\$(vDateFrom)<=\$(vDateTo)"} >} Code)

• ###### problems with a count of nulls

Well, first of all, are you working with version 9.x? I'm working with many different and more complex set analysis syntaxes and they are working ok. Is that the dialog that appears when you add an expression in pivot / straight table?

It doesn't make any sense for me, unless you are working with previous versions of QlikView, when Set Analysis was not supported.

• ###### problems with a count of nulls

my version is 8.01

to the other question i cant do now.... i will upload an example tomorrow..

thanks for all .

• ###### problems with a count of nulls

Then Set Analysis won't work. So gaoing back to conditionals, if your example returns zero or null, then some dates may not be correctly formatted.

• ###### problems with a count of nulls

Hi,

Thank´s

Rainer