8 Replies Latest reply: Mar 25, 2011 6:05 AM by Miguel Angel Baeyens de Arce

# Short order and null values

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

Hello,

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

Regards.

• ###### Short order and null values

Hi Miguel

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

• ###### Short order and null values

Hi Gregorio,

Martijn

• ###### Re. :Short order and null values

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,

• ###### Re. :Short order and null values

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

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

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

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.