Expression in Pivot Chart - sorting issue

[#1]

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

[CELAMBARASAN]

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

[#3]

Sorting will be only for first expression wise.

[#4]

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

[#5]

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

[CELAMBARASAN]

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

[#7]

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

[#8]

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

[CELAMBARASAN]

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

[#10 · Accepted solution]

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