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     Difference between rows

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Difference between rows
 Login/Join
 
Cyril Joy
Platinum Member
posted
Hi,

As we find the SUM of two rows, is there a way to find out the difference between two rows in a report?


Regards,
Cyril Joy.

WF Production 8008 on Linux.
 
Posts: 143 | Location: Rochester,NY. | Registered: August 20, 2004Report This Post
Leah
Virtuoso
posted Hide Post
You can create a computed field that subtracts the one from the other and then total on table
TABLE FILE CAR
SUM RETAIL_COST DEALER_COST
COMPUTE DIFF/D6 = RETAIL_COST - DEALER_COST;
BY CAR
BY COUNTRY
ON TABLE COLUMN-TOTAL
-* the following in this example gives same as column-total
-*ON TABLE SUMMARIZE
END


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Cyril Joy
Platinum Member
posted Hide Post
I need row wise calculation and not column wise.If i have two records say
Englang 105 150
England 100 200

The output must be

England 5 -50 (ie, the difference between the rows).


Regards,
Cyril Joy.

WF Production 8008 on Linux.
 
Posts: 143 | Location: Rochester,NY. | Registered: August 20, 2004Report This Post
Leah
Virtuoso
posted Hide Post
So you want something like
compute diff /d6 = fielda - last fielda
TABLE FILE CAR
SUM RETAIL_COST DEALER_COST
COMPUTE DIFF/D6 = RETAIL_COST - DEALER_COST;
COMPUTE ROWDIF/D6 = IF COUNTRY NE LAST COUNTRY THEN 0 ELSE
RETAIL_COST - LAST RETAIL_COST;

BY COUNTRY
BY CAR
ON TABLE COLUMN-TOTAL
-*ON TABLE SUMMARIZE
END
PAGE 1

COUNTRY CAR RETAIL_COST DEALER_COST DIFF ROWDIF
ENGLAND JAGUAR 22,369 18,621 3,748 0
JENSEN 17,850 14,940 2,910 -4,519
TRIUMPH 5,100 4,292 808 -12,750
FRANCE PEUGEOT 5,610 4,631 979 0
ITALY ALFA ROMEO 19,565 16,235 3,330 0
MASERATI 31,500 25,000 6,500 11,935
JAPAN DATSUN 3,139 2,626 513 0
TOYOTA 3,339 2,886 453 200
W GERMANY AUDI 5,970 5,063 907 0
BMW 58,762 49,500 9,262 52,792
TOTAL 173,204 143,794 29,410 47,658


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Cyril Joy
Platinum Member
posted Hide Post
That is the data I need, but now it is coming as columns. I need the output as rows as we do in the SUM command.

TABLE FILE CAR
SUM RETAIL_COST DEALER_COST
BY CAR
END

I need something like,

TABLE FILE CAR
DIFFERENCE RETAIL_COST DEALER_COST
BY CAR
END

ASSUMING THAT THERE WILL BE ONLY 2 ROWS FOR A CAR.


Regards,
Cyril Joy.

WF Production 8008 on Linux.
 
Posts: 143 | Location: Rochester,NY. | Registered: August 20, 2004Report This Post
Leah
Virtuoso
posted Hide Post
You can put the compute first in the 'row' but I think I'm not fully grasping what you want.

Is it to print two records with the second record having a computed value of the difference in the two records?

A simulation maybe here:
TABLE FILE CAR
PRINT
COMPUTE COUNTER/I2 = IF CAR EQ LAST CAR THEN COUNTER + 1 ELSE 1; NOPRINT
COMPUTE DIFFD/D6 = IF COUNTER EQ 1 THEN 0 ELSE DEALER_COST - LAST DEALER_COST;
COMPUTE ROWDIFR/D6 = IF COUNTER EQ 1 THEN 0 ELSE RETAIL_COST - LAST RETAIL_COST;
RETAIL_COST DEALER_COST
BY CAR
WHERE TOTAL COUNTER LE 2
END


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Craig Kozlow
Member
posted Hide Post
Welcome to te wonderful world of FML (or EMR, or SML, or whatever they're calling it these days!).

The FOCUS 'Financial Modeling Language' allows for these types of inter-row calculations. I believe the following code is what your looking to do:

TABLE FILE CAR
SUM DEALER_COST RETAIL_COST
FOR CAR
JAGUAR AS 'JAGUAR' LABEL JAG OVER
JENSEN AS 'JENSEN' LABEL JEN OVER
BAR OVER
RECAP DIFF = JAG - JEN; AS DIFFERENCE
END

FML itself is quite powerful and takes a little time to get used to, but it works great for these types of calculations, as well as other idiosyncracies of Financial Reporting (P+L, Balance Sheets, etc.)

Craig


PROD:WebFOCUS 7.6.11 on UNIX accessing FOCUS, Oracle, SQLServer
TEST: WebFOCUS 7.7.1 on UNIX accessing FOCUS, Oracle, SQLServer
 
Posts: 28 | Location: USA | Registered: March 24, 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     Difference between rows

Copyright © 1996-2020 Information Builders