topic Re: Rank function in an expression in QlikView

Rank function in an expression

Fri, 10 Feb 2012 14:35:47 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)

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

Rank function in an expression

CELAMBARASAN — Fri, 10 Feb 2012 14:44:04 GMT

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

Re: Rank function in an expression

Fri, 10 Feb 2012 14:45:31 GMT

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

Rank function in an expression

Fri, 10 Feb 2012 15:01:50 GMT

Hi Romi,

I have tried this but it doesn't work.

Thanks,

Gregg

Rank function in an expression

Fri, 10 Feb 2012 15:07:01 GMT

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

Romi

Rank function in an expression

Fri, 10 Feb 2012 15:14:47 GMT

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

Rank function in an expression

Fri, 10 Feb 2012 15:14:47 GMT

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

Re: Rank function in an expression

Fri, 10 Feb 2012 15:16:39 GMT

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

Rank function in an expression

Fri, 10 Feb 2012 15:17:03 GMT

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

Romi

Rank function in an expression

Fri, 10 Feb 2012 15:31:17 GMT

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.

Re: Rank function in an expression

Fri, 10 Feb 2012 16:22:07 GMT

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!

Re: Rank function in an expression

Fri, 10 Feb 2012 16:23:04 GMT

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!

Rank function in an expression

Fri, 10 Feb 2012 16:52:05 GMT

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

Rank function in an expression

CELAMBARASAN — Sat, 11 Feb 2012 06:38:18 GMT

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

Re: Rank function in an expression

Sun, 12 Feb 2012 19:04:10 GMT

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

Re: Rank function in an expression

Fri, 17 Feb 2012 14:53:36 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