Focal Point
Using Rank result in a Define or Compute formula

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/5647060436

October 10, 2013, 03:46 PM
TRue
Using Rank result in a Define or Compute formula
Is there a way to use the result of a Rank column inside the formula of either a Define or a Compute?

As an example, I have ranked 75 lines of data based off of 'CALCGROSS' column. I want to create another column that multiplies the rank value by 2 for Canada and 3 for USA.

As a define the 'logic' is as follows:
` IF BRANCH_COUNTRY EQ 'US' THEN 3 * CALCGROSS Rank ELSE 2 * CALCGROSS Rank `

Obviously this is not a real formula, just using it as a jump off point to get to the right one...

InfoAssist only - Version: 7703
Windows 7
Excel and PDF outputs
October 11, 2013, 09:06 AM
linus
I get my rank and hold it, then query the hold file and perform my compute on the ranking.

```
TABLE FILE HPCTACV
SUM RNK.RS2S_PCT_60 AS 'RS2S_RANK'
BY HIGHEST RS2S_PCT_60
BY DSR
ON TABLE HOLD AS HRS2SRNK
END
-RUN
TABLE FILE HRS2SRNK
SUM RS2S_PCT_60
RS2S_RANK
COMPUTE RS2S_WS/P5.3 = RS2S_RANK * &S2SKPI;
BY DSR
END
```

WF 7.7.05
HP-UX - Reporting Server, Windows 2008 - Client, MSSQL 2008, FOCUS Databases, Flat Files
HTML, Excel, PDF
October 11, 2013, 02:27 PM
TRue
Thanks linus...
But I only use Info Assist. I sometimes 'Edit Source' from the tree but I don't have access to Dev Studio.
Is there any way I can translate this into Info Assist code or a compute formula? I've spent the morning trying to convert yours and using the documentation but it's different enough it crashes my report.

** Extra Info ** Here's the full concept:
There are 5 columns to be ranked by highest values - VOL, GP, MARGIN, FT and AGE.
Those 5 ranks are then weighted - multiplying by 2, 3, 0, 0 and 5, respectively.
The weighted score for each of those columns is then added together to create the FINALSCORE - which is the final, ultimate rank for the entire report.

So, what I'm really looking for is a way in Info Assist to use the rank results in a compute formula. I tried ranking the VOL column, then using RNK.VOL * 2 but it doesn't seem to work.

InfoAssist only - Version: 7703
Windows 7
Excel and PDF outputs
October 14, 2013, 08:58 AM
linus
I don't think you can create a hold file until version 8 of InfoAssist.

WF 7.7.05
HP-UX - Reporting Server, Windows 2008 - Client, MSSQL 2008, FOCUS Databases, Flat Files
HTML, Excel, PDF
October 14, 2013, 03:45 PM
Luiz De Assis
True,

What if you included the logic (CALCGROSS*3 IF USA ELSE CALCGROSS*2) in a define, and then rank the new column? Just an idea.

Luiz
October 16, 2013, 06:01 PM
TRue
I've discovered you can use the rank result in a Compute formula:

1. Create the rank column based off the field of choice. Ie. CALCGROSS - go to Sort - Rank
2. In the compute use the "RNK" prefix with the original field name. Ie. "RNK.CALCGROSS"
3. Hide the CALCGROSS column in the BY section - it's just a repeat of the original CALCGROSS column. Then only the new rank column shows.

My problem now... is that only one rank is working in my report - the first one in the BY section. I have ranked 5 columns, the first one ranks each line 1 through 75, the next 4 columns all have 1 as the rank for every line.

SO...
Does anyone know of a way to make multiple ranks work or how to manually create a rank column - via a Define or Compute?

This message has been edited. Last edited by: TRue,

InfoAssist only - Version: 7703
Windows 7
Excel and PDF outputs