Ranking in/with Multiple Set Analysis...

[#1]

Hey all, I'm having a bit of a 'go around' with Ranking and Set Analysis. Basically what i'm trying to achieve is a Total Ranking straight table showing reps and regions with a few values and a 'overall rank'.... THEN, i'd like that when i select a region, the table would show the original ranks of the reps, as opposed to re-ranking them 1 to 10 or so forth and so on.

There are a few things that I have to consider:

• The "values" are derived from set analysis, and depending on which product it is, it gets multiplied by a specific number (I've already got this working)...
• There are 3 main products, and the multipliers are different for each (I've already got this working)...
• There are some other variables that i'm taking into account as well (I've already got this working)...
• I have 3 seperate tables as well where i have a ranking working fine... but it's this total table that's giving me troubles...

Here is my code (it works to get me the ranks, but again, does not keep the values if i select a region and shorten the set):

AGGR(NODISTINCT {<SALESFORCE={'xxx','xxy'}>}

RANK(
-sum({1<SALESFORCE={'xxx','xxy'},DateKeyCal={$(vMaxGPMMonth)},[PRODUCT NAME]={'XXXX'}>} CALCULATEDRANKFROMSCRIPT) *
if(SALESFORCE='xxx',.1,
if(SALESFORCE='xxy',.30)) -
sum({1<SALESFORCE={'xxx','xxy'},DateKeyCal={$(vMaxGPMMonth)},[PRODUCT NAME]={'XXXX'}>} CALCULATEDRANKFROMSCRIPT) *
if(SALESFORCE='xxx',.6,
if(SALESFORCE='xxy',.1)) -
sum({1<SALESFORCE={'xxx','xxy'},DateKeyCal={$(vMaxGPMMonth)},[PRODUCT NAME]={'XXXX'}>} CALCULATEDRANKFROMSCRIPT) *
if(SALESFORCE='xxx',.3,
if(SALESFORCE='xxy',.6))
,3,1),TerritoryID)

Thanks All,

-Scott

[#2]

Have i stumped the forums? Or did this post just get too far down the chain?

[#3]

Try to be most specific with the problem or share your App for understand

Regards,

[#4]

Hi Scott

You will need to get the region ignored.

<REGION=, SALESFORCE....

I assume you are aware that by specifying "{1" in your set expression you are ignoring ANY selections.

Regards

Jürg

[#5]

Yes, i realized that after i posted... i've since changed it to a dollar sign expansion $.

However, specifing to exclude Region does not work. I've also tried taking each one of those set analysis statements and making them individual variables, as i was thinking with so many set analysis' in the equation, Qlikview was getting confused. That didn't work. Then i tried a bunch of permutations on excluding selections, including trying to exclude the field(s) that the original set analysis statements were based off of. That didn't work. Then i was thinking that maybe the object was corrupt, so i re-created it, repeating ALL of my aforementioned troubleshooting steps. That didn't work.

It's almost as if Qlikview is completely ignoring the set analysis statement... OR, perhaps there's another way i can Rank all of this? What would be something that would throw this off? Pretty much where i'm at right now is that i have the following equation:

AGGR(NODISTINCT
{$<
 Region=,
 TerritoryID=
>}
RANK(
-$(vRank1)-
$(vRank2)-
$(vRank3)
 ,3,1),TerritoryLinkID)

I'm just seperating this on different lines for ease of reading ay.

Thanks,

-Scott

[#6]

Hi Scott

Looks like you have run quite a few variants on this problem.

Just curious - you are aware that field names are CaSe-SeNsItIvE?

Did you try to create a very basic scenario to build upon it? If so, can you share it so we can have a look at what goes wrong.

Ever looked at shared examples from Paul (the one with the +15'000 points?). He makes analyzing easy by providing text boxes with his steps and expectations.

Regards

Juerg