Rank function in an expression

[#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)

The only way possible I can see to acheive this is by using maybe a rank function and rank the results in ascending order.

Do you know if this possible and if so, how?

I am not overly familiar with expressions.

Regards,

Gregg

[CELAMBARASAN]

Hi,

     I posted the answer in you previous post look at in your previous post.

     Rank(Aggr(sum(Activity) / sum (time),Year,Quarter,Month))

Regards

Celambarasan

[#3]

Hi Greg,

You can achieve this by changing the Sort By option for your first dimension in the list. Select the option to sort it by Expression and then copy and paste the expression formula e.g.(formula for the AR expression).

This will sort the entire pivot table based on the result of that formula. Make sure you clear the other options to sort by. See an example in the attached picture.

Regards,

      Romi

[#4]

Hi Romi,

I have tried this but it doesn't work.

Thanks,

Gregg

[#5]

Can you post some screen shot of your table and one with the sort tab?

Romi

[#6]

Gregg,

If you want every single dimension on that pivot table to be sorted by that expresion, you will have to repeat this step for all 3 dimensions. I can send you an example if you want as it works fine for me.

Regards,

     Romi

[#7]

Hi Romi,

I cannot post the information but basically I have dimension on the left that I pivot off:

customer

name

Code

Then on the right I have my expressions.

I set the sort by expression on the customer field but did not work.

Regards,

Gregg

[#8]

Hi Romi,

I tried setting the three also but didn’t work for me.

Can you send me your example and I will cross reference?

Thanks,

Gregg

[#9]

Do the exact same sorting for the other 2 dimensions (name and tmscode) and then it should work.

Romi

[#10]

Here are some screenshots. If you want the actual file, I can email it to you.

Romi

------------------

Screenshot with the Pivot table. Data is from a Qv example. Notice that all the data is sorted by the value of the Total Price field.

This is the formula for the Total Price fields

This is the Sort by option for the first dimension.

This is the Sort by option for the second dimension.

This is the Sort by option for the third dimension.