Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Using Rank result in a Define or Compute formula
Go
New
Search
Notify
Tools
Reply
  
Using Rank result in a Define or Compute formula
 Login/Join
 
Silver Member
posted
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...

Thanks in advance.


InfoAssist only - Version: 7703
Windows 7
Excel and PDF outputs
 
Posts: 34 | Registered: August 30, 2012Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 149 | Location: Dallas, TX | Registered: June 08, 2007Reply With QuoteReport This Post
Silver Member
posted Hide Post
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
 
Posts: 34 | Registered: August 30, 2012Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 149 | Location: Dallas, TX | Registered: June 08, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 117 | Location: Denver | Registered: July 27, 2005Reply With QuoteReport This Post
Silver Member
posted Hide Post
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
 
Posts: 34 | Registered: August 30, 2012Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Using Rank result in a Define or Compute formula

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.