topic problems with a count of nulls in QlikView

problems with a count of nulls

Mon, 29 Mar 2010 12:06:15 GMT

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

Miguel_Angel_Baeyens — Mon, 29 Mar 2010 12:32:53 GMT

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

Tue, 30 Mar 2010 09:49:29 GMT

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

Miguel_Angel_Baeyens — Tue, 30 Mar 2010 10:47:29 GMT

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

Tue, 30 Mar 2010 10:55:18 GMT

i have upload the image of the error...

thanks.

problems with a count of nulls

Tue, 30 Mar 2010 11:02:23 GMT

=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

Tue, 30 Mar 2010 11:43:01 GMT

sorry in english

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

problems with a count of nulls

Tue, 30 Mar 2010 11:49:38 GMT

Hi,

please upload an example (qvw file). It will be easier to get your point.

Thank´s

Rainer

problems with a count of nulls

Miguel_Angel_Baeyens — Tue, 30 Mar 2010 11:52:33 GMT

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

Tue, 30 Mar 2010 12:32:11 GMT

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

Miguel_Angel_Baeyens — Tue, 30 Mar 2010 15:01:25 GMT

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.