topic Re:Ranking in/with Multiple Set Analysis... in QlikView

Ranking in/with Multiple Set Analysis...

Mon, 10 May 2010 22:14:19 GMT

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

Ranking in/with Multiple Set Analysis...

Tue, 11 May 2010 14:50:07 GMT

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

Ranking in/with Multiple Set Analysis...

Tue, 11 May 2010 16:16:02 GMT

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

Regards,

Re:Ranking in/with Multiple Set Analysis...

Tue, 11 May 2010 17:17:59 GMT

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

Re:Ranking in/with Multiple Set Analysis...

Tue, 11 May 2010 22:19:52 GMT

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

Re:Ranking in/with Multiple Set Analysis...

Wed, 12 May 2010 11:45:15 GMT

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