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

[#2]

Hi Romi,

It’s a strange one as I have tried exactluy what you gave me.

I tried the following expression:

Rank(Aggr(Sum(Cases2) / sum(HOURS /60),customer))

I put this into the 3 expression fields and it works for 95 % of the data but for some reason in one of the customer fields data, it is not sorting!

Cant seem to catch a break on this one!

[#3]

Hi Celambarasan Adhimulam,

I tried your expression

Rank(Aggr(Sum(Cases2) / sum(HOURS /60),customer))

It sorts for all of the customers excepts one - not sure why!

[#4]

Greg,

If I were you, I would create a straight table chart  with these 3 dimensions and the expresion formula and inspect the data to make sure it produces the right result. It will be easier to troubleshoot that way.

Romi

[CELAMBARASAN]

Hi,

     Use expression for all dimensions as below.

     For customer:Rank(Aggr(Sum(Cases2) / sum(HOURS /60),customer))

     For name : Rank(Aggr(Sum(Cases2) / sum(HOURS /60),name,Customer))

     For Code : Rank(Aggr(Sum(Cases2) / sum(HOURS /60),code,name,customer))

I believe it will help you

Celambarasan

[#6]

Hi Celambarasan,

I tried using your format below but that doesn’t work.

If I use the original way:

For customer:Rank(Aggr(Sum(Cases2) / sum(HOURS /60),customer))

For name : Rank(Aggr(Sum(Cases2) / sum(HOURS /60),name))

For Code : Rank(Aggr(Sum(Cases2) / sum(HOURS /60),code))

I get it sorted but the last problem I have is:

It seems to format everything ok except for when a user is listed twice. For example if user1 has a rate for customer1, it will sort the rate according to this customer.

However, if user1 also has a rate for customer2, it wont sort this rate.

So basically it seems to not sort a second instance of a rate for the same user,

Regards,

Gregg

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