(Solved)Difference between subtotals

August 27, 2012, 11:32 AM

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

August 27, 2012, 01:04 PM

Doug

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

August 27, 2012, 02:24 PM

Prarie

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?

August 27, 2012, 03:13 PM

Doug

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

) 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)?

August 27, 2012, 03:38 PM

Prarie

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.

August 27, 2012, 04:12 PM

Dan Satchell

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

August 27, 2012, 04:15 PM

Doug

I like it... Is there anything noteworthy special in your -INCLUDE of ENDEFLT.STY?

August 27, 2012, 04:18 PM

Dan Satchell

ENDEFLT.STY is a sample style sheet from IBI included with other sample WebFOCUS files.

WebFOCUS 7.7.05

August 27, 2012, 05:37 PM

Prarie

Hey Dan that looks real promising. Let me try it in the real life report and I will report back.

Thanks

August 28, 2012, 10:07 AM

Prarie

Thanks Dan! That did the trick..appreciate your help.

August 28, 2012, 02:32 PM

Tony A

Brain freeze? In Texas?!?!?

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

August 28, 2012, 02:40 PM

Prarie

Sure!..it can get right chilly...in my head