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

[cafcptg2011]

Imagine this situation:

Your expression gives the region B. But, the correct bottom region is M, because it has sales = 0 and have the lowest orders value, right?

I also need to change my bottom pivot table. do you understand my pb?

thanks in advance,

regards

CAFC

link to qvw file : getMinimumValue1.qvw

[anlonghi2]

Hi cafcPTG2011,to obtain the expected result (Region=e) you have to set the variable to -1 because the formula select only the regions with a number of orders greater than the variable value. If you set the variable to 0 the formula will return the max ranked region of the ones with a number of orders greater than 0 so t is correct (I think, of course :-))

[#4]

Try this

=Aggr(if(Rank(Sum({<Orders={'>0'}>}Sales)&Sum({<Orders={'>0'}>}Orders),4,2)=count(Total   {<Orders={'>0'}>} Region),Only({$<Orders={'>0'}>}Region)),Region)

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

If you want to go to the next bottom one do

=Aggr(if(Rank(Sum({<Orders={'>0'}>}Sales)&Sum({<Orders={'>0'}>}Orders),4,2)=count(Total   {<Orders={'>0'}>} Region)-1,Only({$<Orders={'>0'}>}Region)),Region)

This works will all the sample files you provided.

Please check the getMinimumValue_Preethi.qvw file for reference. The tab is correct calculation,

[cafcptg2011]

thank you very much for your reply,

but now I have a pb 🙂 because my bottom pivot should be consistent to your expression, the bottom must be like this:

and not like this:

my bottom current dimension expression is:

aggr(avg( aggr(rank( (sum({$<Region=, Orders={'>0'}>}Sales)),4,1),Region)),Region)<=$(vLimit),Region)

could you please help me to modify my expression in order to get correct bottom?

Thank you very much again for your overwhelming help!!

Regards,

CAFC

[#6]

Andrea,

yours is not working correctly when I try to apply it to the latest qvw that I got from cafcPTG2011.

I have attached the file for your ref with your variables. Please check the tab named Andrea.

[#7]

I'm sorry, but I dont udnerstand what your bottom does. I'm assuming it should be N,M,J?? If not I dont know how you are getting J,N,Q,M.

Also in future please make sure you pose all your requirements correctly in the first place, so it will be easy to give you a solution instead of going bck and forth.

[cafcptg2011]

i have tested your expressions and the first one gives me M (and not N) and the second one returns Q, that's correct!!

look to this picture:

So logically the bottom should be M, Q N, J, etc, right?

my qvw file here: getMinimumValue2.qvw

thank you very much

[#9 · Accepted solution]

Please check the attached. This data is completely different than what you gave before. It is very hard to help fix the issue if you keep changing the data. Please be consistent in the future.

[cafcptg2011]

sorry about that, my first qvw wasn't enough close to my real data, so i needed to change little bit my qvw, sorry.

yes, its precisely what I need.

I have a ultimate question because i didn't find in the qv forum neither on the google that is to return to a text box the rank position number, for instance, for the region B the global rank number is 9, for region M is 13, and so on...

Could you please help on my last question.

I'm sorry for my insistence.

thank very much again

[#11]

I'm assuming that you will be selecting a region for this so that global rank will be displayed in the text box?

use this:

=aggr(rank( sum( {$<Region=>} Sales),4,1),Region)

Also since I answered your original question, cna u please mark this as answered?