get minimum dimension of bottom ranking

[cafcptg2011]

Hi,

I need to get the minimum region dimension of bottom ranking, in this case is the Region e. How can I accomplish that?

I have attached a qvw file to help figure out my pb more easily.

Could anyone please help on this?

Thanks in advance,

CAFC

[preminqlik]

hi there in text box try this

=subfield(concat(Aggr(if(rank(-sum(aggr(sum(Sales),Region)),0,1)<2,Region),Region),'!'),'!',-1)

[cafcptg2011]

hi,

first of all thank you very much for your reply.

yes, your expression returns the region e.

but,

1st - I need to filter the sales with the orders >0 and when i change your expression to :

=subfield(concat(Aggr(if(rank(-sum(aggr(sum( { < Orders ={'>0'} > } Sales),Region)),0,1)<2,Region),Region),'!'),'!',-1) it gives the region f , and region f does not have orders neither makes part of bottom ranking.

2nd : for instance, if I need to obtain not the region e but the second bottom region, lets say the region g, how can I accomplish that,

thanks

regardes,

CAFC

[#4]

Try using this:

=MaxString({$<Sales={"$(=min(total Sales))"}>}Region)

[#5]

Hi,

Use this

=Aggr(if(num(rank(-sum({$<Orders={'>0'}>}Sales),4,2))=1,Only({$<Orders={'>0'}>}Region)),Region)

in your text box.

For your issues:

1st - I need to filter the sales with the orders >0 and when i change your expression to :

I have used the Orders>0 in the set to filter the sales.

2nd : for instance, if I need to obtain not the region e but the second bottom region, lets say the region g, how can I accomplish that,

All you have to do is in your "if" comparision you have to use 2 instead of 1

=Aggr(if(num(rank(-sum({$<Orders={'>0'}>}Sales),4,2))=2,Only({$<Orders={'>0'}>}Region)),Region)

This gives you the second bottom one.

Please let me know if this works.

[anlonghi2]

Hi cafcPTG2011, look at attached app and give me a feedback, please.

Best regards

Andrea

[cafcptg2011]

Hi Preethi Mohan!!

Thank you very much for your reply

Yes, it works nice. but, when I put my expression to my real qvw I came across with a simple problem that I can't figure out one solution to that. I need to get the minimum region with lowest order value.

The current result is B (with your expression) and is correct. Because, B is indeed the first region with sales = 0 and orders >0 .

But, the result I need is N, Because, in case of a draw between sales region = 0 , I need to get the region with lowest positive orders.

the link for my qvw file: getMinimumValue.qvw

Thanks in advance,

Regarde,

CAFC

[cafcptg2011]

Hi Andrea!

Thanks for your reply.

If I change the variable number of orders to 0 then the result region is t, but t doesn't makes part of the bottom ranking, right?

thank you very much.

regardes,

CAFC

[#9]

I dont understand,

"in case of a draw between sales region = 0 , I need to get the region with lowest positive orders."

B, J, M, N - all the four have the lowest positive orders which is 1 . It is taking the smallest of all the four which is B. Do you want to take it the highest of all the 4 which is N?

[cafcptg2011]

yes, need to get the N region. what I mean is that between regions with sales = 0 I need to get the lowest orders region value, which is N (which corresponds to highest ranking of all the 4).

thanks