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     (Solved)Difference between subtotals

Read-Only Read-Only Topic
Go
Search
Notify
Tools
(Solved)Difference between subtotals
 Login/Join
 
Virtuoso
posted
I have a power user that has asked for help.
They want to see the difference between sub-totals on each grouping of average totals.
Then have it in a separate column.

I have simplfied what they want with the Car file.
So if you were to run this they would want to know what the difference between the subtotal of Alfa Romeo and Audi is in a separate column.
Then go down to the next two and diff of those.
The in separate column is not that necessary as just the diff of the two

Any thoughts on how i might do this?

  DEFINE FILE CAR
NEWAVG/D20.2 = 0;
END
TABLE FILE CAR
SUM
COMPUTE PRICE_AVG/D20.4 = AVE.SALES/AVE.DEALER_COST;
NEWAVG AS 'THIS WOULD BE THE NEW VALUE'
BY CAR  
ON CAR RECOMPUTE PRICE_AVG AS 'Sub-Total'

END

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


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Expert
posted Hide Post
Hi Prarie. Have you considered the use of "LAST", as follows:
TABLE FILE CAR
SUM
COMPUTE PRICE_AVG/D20.4 = AVE.SALES/AVE.DEALER_COST;
COMPUTE AVG_DIF/D20.4 = PRICE_AVG - LAST PRICE_AVG ;
BY CAR  
END

Results:
CAR PRICE_AVG PRICE_DIF 
ALFA ROMEO  1.8601   1.8601 
AUDI        1.5406   -.3195 
BMW         1.6240    .0834 
DATSUN     16.3747  14.7507 
JAGUAR       .6444 -15.7303 
JENSEN       .0000   -.6444 
MASERATI     .0000    .0000 
PEUGEOT      .0000    .0000 
TOYOTA     12.1379  12.1379 
TRIUMPH      .0000 -12.1379 
Does that work for you?




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Virtuoso
posted Hide Post
Hi Doug.

i was hoping for something as simple as that, but see they want the difference between last Subtotal...not last occurance.
You might have 4 prices per car and they want the difference between the two cars subtotal. Crazy huh?
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Expert
posted Hide Post
Crazy? Yeah. I should have known that this wasn't going to be as easy as I presented it. After all, it came from YOU and you would have figured it out, unless you had the proverbial brain freeze that we sometimes get. (Actually, I didn't realize that it was YOU, until AFTER I posted it Roll Eyes ) So, let's dig a bit deeper.

About the "You might have 4 prices per car": Would that be, in this case, one for each model (supposing that there are 4 models of that car)?
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Virtuoso
posted Hide Post
Yes the ole brain freeze..that is what I was hoping it would be.
There might be 4 prices per car...and then a subtotal. Then there is the next car...with say 3 prices then subtotal. And these are averages so you need the recompute.

They want to know the Difference between those two subtotals.
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Virtuoso
posted Hide Post
Maybe something like this:

DEFINE FILE CAR
 DIFFERENCE/D20.4S = 0 ;
END
-*
TABLE FILE CAR
 SUM
  SALES
  DEALER_COST
  COMPUTE PRICE_AVG/D20.4 = AVE.SALES/AVE.DEALER_COST;
  DIFFERENCE
 BY COUNTRY
 BY CAR  
 ON COUNTRY RECOMPUTE PRICE_AVG AS 'Sub-Total'
 ON COUNTRY COMPUTE COUNTER/I5 = LAST COUNTER + 1 ;
 ON COUNTRY COMPUTE DIFF/D20.4S = IF (IMOD(COUNTER,2,'I5') EQ 0) THEN PRICE_AVG - LAST PRICE_AVG ELSE 0 ;
 ON COUNTRY SUBFOOT
  " <+0> <+0> <+0> <+0> <DIFF "
  WHEN DIFF NE 0 ;
 ON TABLE NOTOTAL
 ON TABLE SET LINES 9998
 ON TABLE SET STYLE *
-INCLUDE ENDEFLT.STY
  TYPE=REPORT, HEADALIGN=BODY, $
  TYPE=SUBFOOT, JUSTIFY=RIGHT, $
 ENDSTYLE
END

I used IMOD to calculate the difference for even-numbered groups only.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Expert
posted Hide Post
I like it... Is there anything noteworthy special in your -INCLUDE of ENDEFLT.STY?
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Virtuoso
posted Hide Post
ENDEFLT.STY is a sample style sheet from IBI included with other sample WebFOCUS files.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Virtuoso
posted Hide Post
Hey Dan that looks real promising. Let me try it in the real life report and I will report back.

Thanks
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Virtuoso
posted Hide Post
Thanks Dan! That did the trick..appreciate your help. Smiler
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Expert
posted Hide Post
Brain freeze? In Texas?!?!?

Wink

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Virtuoso
posted Hide Post
Sure!..it can get right chilly...in my head Wink
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 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     (Solved)Difference between subtotals

Copyright © 1996-2020 Information Builders