[SOLVED] Sum only for certain values

Guru

posted

Is there a way to compute a difference for two of the across columns?

Release: WebFOCUS 7.6.8
OS: Windows
Output formats: HTML, PDF, Excel, csv

Posts: 256 | Registered: July 21, 2008

Ignored post by ChannyS posted

GamP

Virtuoso

posted

Yes there is.

The exact code depends on what your reuqest looks like, since the method uses relative column adressing.

GamP

- Using AS 8.2.01 on Windows 10 - IE11.
	in Focus since 1988

Posts: 1961 | Location: Netherlands | Registered: September 25, 2007

Ignored post by GamP posted

GamP

Virtuoso

posted

For instance this request:

TABLE FILE CAR
SUM RCOST
BY SEATS
ACROSS COUNTRY
COMPUTE DIFF/D12 = C2-C1;
IF COUNTRY EQ 'ITALY' OR 'ENGLAND'
END

It gives you the difference of the two columns.

GamP

- Using AS 8.2.01 on Windows 10 - IE11.
	in Focus since 1988

Posts: 1961 | Location: Netherlands | Registered: September 25, 2007

Ignored post by GamP posted

Guru

posted

That works great ( I just adjusted the column numbers for my report).

The output I get is:

COUNTRY
ENGLAND ITALY DIFF
SEATS
-------------------------------------
2 13,978 45,140 31,162
4 17,850 5,925 -11,925
5 13,491 . -13,491

I will be shutting off COUNTRY by doing an AS '' for the ACROSS. Is there any way to do the following:
1) Rename the ENGLAND and ITALY columns?
2) Get the ENGLAND and ITALY titles to be on the same line as SEATS?

Release: WebFOCUS 7.6.8
OS: Windows
Output formats: HTML, PDF, Excel, csv

Posts: 256 | Registered: July 21, 2008

Ignored post by ChannyS posted

Guru

posted

Also, another thing I'd like to be able to do...

After the dif column for the sum of RCOST by SEATS Across countries, I'd like SUM RCOST for the same countries across BY CAR within each BY SEAT.

For example, for the 2 SEATS, I'd like to then see which cars have 2 SEATS in England and Italy and the SUM of their RCOST - is there any way to do that in the same report?

Release: WebFOCUS 7.6.8
OS: Windows
Output formats: HTML, PDF, Excel, csv

Posts: 256 | Registered: July 21, 2008

Ignored post by ChannyS posted

j.gross

Virtuoso

posted

For the first, use define rather than across:

DEFINE FILE CAR
  ENGLAND/D5=RCOST * (COUNTRY IS 'ENGLAND');
  JAPAN  /D5=RCOST * (COUNTRY IS 'JAPAN  ');
  DIFF   /D5=ENGLAND-JAPAN;
END
TABLE FILE CAR
SUM RCOST ENGLAND JAPAN DIFF
BY SEATS
ON TABLE SUMMARIZE
END

For the second, I only got so far:

Using multiple verbs you can get a correct total RCOST (over all countries), while limiting the scope of detail rows produced by the second verb to selected countries by means of IF/WHERE TOTAL:

DEFINE FILE CAR
  ENGLAND/D5=RCOST * (COUNTRY IS 'ENGLAND');
  JAPAN  /D5=RCOST * (COUNTRY IS 'JAPAN  ');
END
TABLE FILE CAR
SUM RCOST ENGLAND JAPAN COMPUTE DIFF/D5=ENGLAND-JAPAN;
  BY SEATS
SUM RCOST
  BY SEATS
    BY COUNTRY
    BY CAR
  IF TOTAL COUNTRY EQ ENGLAND OR JAPAN
-*ON COUNTRY SUMMARIZE
END

But if you add subtotals, you get phantom subtotal lines for the other countries.

And if you change BY CAR to ACROSS CAR, even w/o totaling other things break -- IF TOTAL and ACROSS do not seem to mix well.

This message has been edited. Last edited by: j.gross,

- Jack Gross
WF through 8.1.05

Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005

Ignored post by j.gross posted

Guru

posted

Perhaps I wasn't clear with what I was trying to do but with your first suggestion I was easily able to do it as follows:

DEFINE FILE CAR
ENGLAND/D5=RCOST * (COUNTRY IS 'ENGLAND');
JAPAN /D5=RCOST * (COUNTRY IS 'JAPAN ');
DIFF/D5=ENGLAND-JAPAN;
END
TABLE FILE CAR
SUM ENGLAND JAPAN DIFF
BY SEATS
SUM ENGLAND JAPAN DIFF
BY SEATS
BY CAR
END

Release: WebFOCUS 7.6.8
OS: Windows
Output formats: HTML, PDF, Excel, csv

Posts: 256 | Registered: July 21, 2008

Ignored post by ChannyS posted