Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Rolling Subtraction

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Rolling Subtraction
 Login/Join
 
Guru
posted
We want to create a report that looks like this:

             Apr 23 Apr 24 Apr 25 ...
Text1        1000      
Text2                    5     30
             -------------------------
Rolling Sub1  1000      995    965 ...

Text3         2000      
Text4                   100      
             -------------------------
Rolling Sub2  2000     1900   1900 ...


The dates are values in a field of a database so an across looks promising. The Text comes from a field as well. It the rows beginning with Rolling Sub that is the problem.

As in the example they take the first row and subtract the second row. As well the subtraction has to keep rolling on. In other words, my numbers get smaller and smaller.

Any ideas?

Fernando

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


Prod WF 8.1.04, QA WF 8.2.03, Dev WF 8.2.03
 
Posts: 278 | Registered: October 10, 2006Report This Post
Virtuoso
posted Hide Post
FRL
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Expert
posted Hide Post
FML, as of today, anyway!!! Big Grin

Unfortunately, I "do" remember FRL...


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Guru
posted Hide Post
Ok FML to do the row subtraction.
How would I do the rolling subtraction?

In other words How do I code this?

row3 column x = row 1 column 1 - Sum (row 2 column [1 - x])

Fernando


Prod WF 8.1.04, QA WF 8.2.03, Dev WF 8.2.03
 
Posts: 278 | Registered: October 10, 2006Report This Post
Expert
posted Hide Post
Fernando,

If you don't know FML, it may take awhile. If you want to teach yourself, when you open up a report in the GUI, there is a MATRIX button, that is FML from the GUI. You can also hand-code, but, learn the GUI first.

The main thing is to have get your data to an aggregated level, this is key. Then, you identify data via ROW labels, i.e., R100, R101, etc.

Example of GUI outputed code(snippet):

 
TABLE FILE SPECIAL_FML
SUM
     MTDMSN
     MTDMSO
     MTDAFD
     MTDCCD
     MTDGUD
     MTDMSD
     MTDPPD
     MTDVID
FOR
     CATEGORY
'BILLDAYS'           AS 'Billing Days'       LABEL R1  OVER
'UNITS'              AS 'Actual Units'       LABEL R2  OVER
'UAVG'               AS 'Average Units'      LABEL R3  OVER
'UASS'               AS 'Assigned units'     LABEL R4  OVER
'UAVL'               AS 'Available Units'    LABEL R5  OVER
'MILT'               AS 'Company Miles'      LABEL R6  OVER
" "                  AS " "                  LABEL R7  OVER
'Driver Wages'       AS 'Driver Wages'       LABEL R8  OVER
'Driver Mileage Pay' AS 'Driver Mileage Pay' LABEL R9  OVER
'Driver Per Diem'    AS 'Driver Per Diem'    LABEL R10 OVER
'BAR'                AS 'BAR'                LABEL R11 OVER
     RECAP R12/P16.3BC=R9+R10;  AS 'Total Driver Mileage Pay' OVER


The above data is aggregated up to the CATEGORY level, so, for all the columns that you are SUMming,the RECAP will add R9+R10
and place the totals in R12, for each column. This creates a SUMMARY row.

You'll need to read up on FML, but, once you get going, it is a lot of fun!!!

HAVE FUN!!!

Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Expert
posted Hide Post
Another way would be to do the total line separately with a couple of phony sort fields to put the data in order. You can use a COMPUTE to calculate the new field. Then use the MORE, i.e. Universal Calculation to put the files together.

DEFINE FILE filename
SORTFLD/I1=2;
END
TABLE FILE filename
SUM
COMPUTE TEXTCNTR/I9=IF DATE NE LAST DATE THEN TEXTNUM ELSE LAST TEXTCNTR - TEXTNUM; AS TEXTNUM
BY SORTFLD
BY DATE
BY TEXTSORT
ON TABLE HOLD AS TXTHOLD2 FORMAT ALPHA
END
DEFINE FILE TEXTFILE
SORTFLD/I1=1;
END
TABLE FILE TEXTFILE
SUM TEXTNUM
BY SORTFLD
BY TEXTSORT
ACROSS DATE
MORE
FILE TXTHOLD2
END


You'll have to play with this a bit. Not sure of the sort order for the first hold but it will be apparent to you. Also look up MORE to get the restrictions on it as far as column names and formats as such.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Platinum Member
posted Hide Post
way back I posted in the thread http://forums.informationbuild...561096512#7561096512

a method of using compute do a rolling average. The same idea should work for a rolling subtraction.


Jim Morrow
Web Focus 7.6.10 under Windows 2003
MVS 7.3.3



 
Posts: 129 | Registered: June 01, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Rolling Subtraction

Copyright © 1996-2020 Information Builders