topic Expression in Pivot Chart - sorting issue in QlikView

Expression in Pivot Chart - sorting issue

Fri, 10 Feb 2012 11:32:49 GMT

Hi,

I have created a pivot chart using 3 dimensions with several different rates calculated as expressions.

The issue I am having is that I need to sort the pivot by one of the expressions e.g. (AR)

I cannot sort the pivot in the usual manner (using the sort tab) as this only affects the dimensions and not the expressions.

Is there a possibility to add a sort to my expressions?

They are simple expressions (e.g.) sum(Activity) / sum (time)

Regards,

Gregg

Expression in Pivot Chart - sorting issue

CELAMBARASAN — Fri, 10 Feb 2012 11:46:53 GMT

Hi,

You can do that using the Y value option to ascending or descending in the sort tab of the chart properties.

Did you checked it?

Celambarasan

Expression in Pivot Chart - sorting issue

Fri, 10 Feb 2012 11:52:25 GMT

Sorting will be only for first expression wise.

Expression in Pivot Chart - sorting issue

Fri, 10 Feb 2012 11:53:19 GMT

Hi,

If I use the sort tab of the chart properties it only affects the dimensions in my chart - not the expressions.

I have several expressions in my chart and I need to sort the chart by one of the expressions.

Regards,

Gregg

Expression in Pivot Chart - sorting issue

Fri, 10 Feb 2012 11:55:16 GMT

Hi swatantra.

Im not sure what you mean, can you eplain further?

I have a few expressions but need to sort using one expression.

Thanks,

Gregg

Expression in Pivot Chart - sorting issue

CELAMBARASAN — Fri, 10 Feb 2012 11:58:56 GMT

Hi,

You can do this type of sort for straight table but its not possible in pivot because its drilling through multiple dimension.If possible make your chart to straight table.

Regards

Celambarasan

Expression in Pivot Chart - sorting issue

Fri, 10 Feb 2012 12:03:47 GMT

Thanks Celambarasan Adhimulam.

I was hoping there would be a way to do it in the pivot chart but from what I have read I think the solution as you said is to move to a straight table.

I will look into this and try to keep the same formatting.

Regards,

Gregg

Expression in Pivot Chart - sorting issue

Fri, 10 Feb 2012 14:32:57 GMT

Do you think there is a way I could use a function for the expression to allow me to achieve this?

I am not too familiar with expressions.

The idea being to use a rank function and show the highest value first etc.

Regards,

Gregg

Expression in Pivot Chart - sorting issue

CELAMBARASAN — Fri, 10 Feb 2012 14:39:49 GMT

Hi,

Then use sort by expression as

=Rank(aggr(Expr to be sort,all dimensions))

Example: Rank(Aggr(Sum(Sales),Year,Quarter,Month)) here Sum(Sales) is the expression to be sorted and year,Quarter,month are the dimensions used in pivot table

Celambarasan

Expression in Pivot Chart - sorting issue

Fri, 17 Feb 2012 14:47:57 GMT

Hi,

My issue was that there in some circumstances was a total by the same person for 2 customers which gave them a TOTAL rate.

this total rate was across every customer so when trying to sort the column it would be inconsistent as it was not per individual customer.

Thanks,

Gregg

Re: Expression in Pivot Chart - sorting issue

Tue, 27 Jan 2015 16:20:16 GMT

I have a somewhat similar issue.. I have a pivot table detailing sales data (company, rep, close date, product, etc.) The last dimension is the Year-Month in which the sale closed, and I pulled that up as a horizontal dimension.

It used to work fine, but now the YearMonth sort is all over the place (instead of ascending, which is what we want).

How do I ensure the horizontal dimension is sorted properly? It is a text field "2012-Jun", "2013-Feb", etc. but I created a unique number field for each month (from (Year(CloseDate)-2010)*12+Month(CloseDate) called OMN. When I tried to sort the last dimension using expression = OMN, it didn't make a difference - the months were all over the place.

Any thoughts?