topic Re. :Short order and null values in QlikView

Short order and null values

Thu, 24 Mar 2011 12:35:53 GMT

Hi,

I create a pivot table whit 3 dimensions and for each dimension is defined an order, the pivot table don´t clear ceros or null values so we can always see the report structure.

The problem is, when the expresion isn't cero the order is correct, but whe the expresion is cero the order is worng like

Dimemensio 1 Dimension 2 Dimension 3 Result

Ingresos Ventas A 1500

Ingresos Ventas B 3000

Ingresos Ventas C 1000

when exist 0 value:

Ingregos Ventas B 0

Ingresos Ventas A 2000

Ingresos Ventas C 100.

Any idea to solve the problem.

Thanks a lot.

Short order and null values

Miguel_Angel_Baeyens — Thu, 24 Mar 2011 13:25:34 GMT

Hello,

What field are you using to sort? Are you sorting by Y-Value?

Regards.

Short order and null values

Thu, 24 Mar 2011 14:22:17 GMT

Hi Gregorio,

Sorry but do you have some more information?

What are your order settings?

Martijn

Re. :Short order and null values

Thu, 24 Mar 2011 14:53:30 GMT

Hi Gregorio,

i tried with your example and have the same result but, if you look in your second sample, in dimension 1, the value is "Ingregos" instead of "ingresos", so, your sort works normally.

If i replace "Ingregos" by "ingresos", result is:

Ingresos Ventas A 2000

Ingresos Ventas B 0

Ingresos Ventas C 100.

Please, tell me if it was just a problem with your data or you really have a problem with sort options.

Kindly,

Short order and null values

Thu, 24 Mar 2011 17:43:12 GMT

Hi Miguel

I use to short another field who it's genereted for each dimension.

Thanks for your interest

Re. :Short order and null values

Fri, 25 Mar 2011 07:56:49 GMT

Hi Gregory,

I think that the problem is the qlikview data logic structure, when the expresion result is cero the sort orders fields are empty so the dimensions value apear disordered, any idea to solve the problem????

Thank's a lot.

Re. :Short order and null values

Miguel_Angel_Baeyens — Fri, 25 Mar 2011 08:47:10 GMT

Hello Gregorio,

First of all, I'd uncheck the "Orden de Carga" sort order, so it will only be sorted by Expression. If zero is a possible value of the expression and you don't want it, write a conditional in the Expression sort

If([Criterio de ordenación nivel 1] = 0, 1, [Criterio de ordenación nivel 1])

Hope this helps.

Re. :Short order and null values

Fri, 25 Mar 2011 09:51:03 GMT

Hi Miguel,

The report structure it´s defined in a table who is imported by the conector, this table include the dimensions an the sort orders field for each dimension, so the dimensions sort order is always be defined by the sort orders field but when the expresion is 0 means that sort order fields are null, you have any idea to avoid this behavior.

I Attach a table relationship:

Thank's a lot.

Re. :Short order and null values

Miguel_Angel_Baeyens — Fri, 25 Mar 2011 10:05:47 GMT

Hello Gregorio,

If there are some aggregations for what the value of the expression returns null (think of Sales by month, with no sales in August) the result may be null.

Perhaps you need to use another sort field. Try with a straight table to see what value is returning any of the sort criteria.

Hope that helps.