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] Quarter totals at the end of a row

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Quarter totals at the end of a row
 Login/Join
 
Gold member
posted
Looking to create a report in this format:

        Jan Feb Mar Apr.........Dec Q1_SUM Q2_SUM Q3_SUM Q4_SUM YTD
blue    1   3   2   1           1   6      4      4      12     26 
red     0   4   2   0           0   6      0      0      0      6



I can get the across months but I cant seem to get the row totals to break out into quarters. Any ideas?

Thanks,
Daniel

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


In Focus since 2012
WebFOCUS 8.0.07
Windows, All Outputs
 
Posts: 59 | Registered: November 15, 2012Report This Post
Virtuoso
posted Hide Post
Daniel

This is a general concept. Definitely needs a bit more work. Stil...
  
TABLE FILE ...
SUM VAL BY COLOR BY MONTH
ON TABLE HOLD AS TEMP FORMAT ALPHA
END
FILEDEF TEMP DISK TEMP.FTM (APPEND
DEFINE FILE ...
QTR/A3 = DECODE MONTH(JAN Q1 FEB Q1 MAR Q1 APR Q2 ...);
END
TABLE FILE ...
SUM VAL 
BY COLOR BY QTR
ON TABLE SAVE AS TEMP
END
TABLE FILE TEMP
SUM VAL
BY COLOR ACROSS MONTH
END


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Gold member
posted Hide Post
Thanks for the response Daniel. I am able to get my hold file to look like this:

MONTH | TYPE | QUARTER
JAN     RED    Q1
JAN     BLUE   Q1
APR     BLUE   Q2
MAY     GREEN  Q2


Would this make getting the quarter row totals easier than having to append to a separate hold file and other calculations you're doing in your example?

Daniel


In Focus since 2012
WebFOCUS 8.0.07
Windows, All Outputs
 
Posts: 59 | Registered: November 15, 2012Report This Post
Virtuoso
posted Hide Post
Daniel,

The reason for doing the "calculations" is that you need to generate separate sums for months and quarters.
Don't shy away from HOLD files: they are one of the best features of WF.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Member
posted Hide Post
Danny-SRL,

I understand how to get the sums for months and the sums for quarter, but my problem is getting the months and the quarters to show up on the same row on the across.

Could you help me out with that? Since I'm just learning WebFOCUS, an example with the datamart CARS would be awesome if that's possible.


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 18 | Registered: September 13, 2012Report This Post
Virtuoso
posted Hide Post
Accros quarter
Accros month

And you should be able to do a subtotal on the quarter




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Gold member
posted Hide Post
Thanks Frank,

Across quarter and across month works but it puts the report format in a way that my end user will not accept. The format they require is as follows:

        Jan Feb Mar Apr.........Dec Q1_SUM Q2_SUM Q3_SUM Q4_SUM YTD
blue    1   3   2   1           1   6      4      4      12     26 
red     0   4   2   0           0   6      0      0      0      6


This is where I'm getting stuck. I can get across MONTH number really easy, it's putting the quarter numbers at the end of the row is this problem. Here is the code that I use to get the month munbers:

TABLE FILE .....
SUM
	CNT.COLOR AS ''
ACROSS MONTH AS ''
BY
        COLOR AS ''
END


Here's my hold file structure:
MONTH | TYPE | QUARTER
JAN     RED    Q1
JAN     BLUE   Q1
APR     BLUE   Q2
MAY     GREEN  Q2


Any ideas on how to get the quarter totals at the end of the row? Thanks to everyone for the help.

Daniel


In Focus since 2012
WebFOCUS 8.0.07
Windows, All Outputs
 
Posts: 59 | Registered: November 15, 2012Report This Post
Gold member
posted Hide Post
I finally go this to work....is it the right way? I don't know but this is how I did it.


-*GET QUARTER NUMBERS
TABLE FILE .....
SUM
     CNT.COLOR AS ''
BY  COLOR AS ''
ACROSS QUARTER AS ''
ON TABLE HOLD AS QUARTER_DATA
END

-*GET MONTH NUMBERS
TABLE FILE ....
SUM
     CNT.COLOR AS ''
BY  COLOR AS ''
ACROSS MONTH 
ON TABLE HOLD AS MONTH_DATA
END

-* MATCH BOTH HOLD FILES BASED ON COLOR
MATCH FILE MONTH_DATA
  PRINT 01 AS 'Jan' 02 AS 'Feb' 03 AS 'Mar' 04 AS 'Apr' 05 AS 'May' 06 AS 'Jun' 07 AS 'Jul' 08 AS 'Aug' 09 AS 'Sep' 10 AS 'Oct' 11 AS 'Nov' 12 AS 'Dec'
  BY MONTH_DATA.MONTH_DA.COLOR AS ''
RUN
FILE QUARTER_DATA
  PRINT Q1 Q2 Q3 Q4
  BY QUARTER_DATA.QUARTER_.COLOR AS ''
AFTER MATCH HOLD AS COMBINED_DATA OLD-OR-NEW
END

-* I NOW HAVE MY RESULTS IN THE FORMAT THAT WAS REQUESTED

        Jan Feb Mar Apr.........Dec Q1_SUM Q2_SUM Q3_SUM Q4_SUM 
blue    1   3   2   1           1   6      4      4      12      
red     0   4   2   0           0   6      0      0      0      


If anyone knows of a better way of achieving this result format please let me know.

Daniel


In Focus since 2012
WebFOCUS 8.0.07
Windows, All Outputs
 
Posts: 59 | Registered: November 15, 2012Report This Post
Guru
posted Hide Post
You can't do it with a basic table request. You have to use the technique Danny described.
You are building the dataset using APPEND. The quarters become pseudo months.

Here is what the dataset will look like
  
COLOR   COUNT  MONTH
-----   -----  -----
RED     5      Jan
BLUE    6      Feb   
GREEN   7      Mar
RED     20     Q1
GREEN   15     Q1
BLUE    12     Q2


TABLE FILE TEMP
SUM
   COUNT
BY COLOR
ACROSS MONTH
END

Can you picture it now?


WebFOCUS 8.1.05M Unix Self-Service/MRE/Report Caster - Outputs Excel, PDF, HTML, Flat Files
 
Posts: 320 | Location: Memphis, TN | Registered: February 12, 2008Report This Post
Guru
posted Hide Post
Looks good. I thought you wanted to achieve this with an ACROSS.
Now your table request for your report will look like this. And its not using an ACROSS.
TABLE FILE COMBINED_DATA
PRINT
     Jan
     Feb
     Mar
     ...
     ...
     Q1
     Q2
     Q3
     Q4
BY COLOR
END

There's more than one way to skin a cat.


WebFOCUS 8.1.05M Unix Self-Service/MRE/Report Caster - Outputs Excel, PDF, HTML, Flat Files
 
Posts: 320 | Location: Memphis, TN | Registered: February 12, 2008Report This Post
Virtuoso
posted Hide Post
There are several ways to achieve this.
You may take a look at the multiverb functionality, but if your solution works it should be ok.

Also with mcguyver it could be done.

Multiverb:
Table file car
Sum sales
By country
Sum sales
By country
Across model
End



This is a simple multiverb report, but it might give you an idea




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Gold member
posted Hide Post
Mighty Max,

I would have liked to us ACROSS but it just wasn't giving me the results in the format that my end users wanted. That is why I ended up making two hold files matching them and then just print out to the screen.

Daniel


In Focus since 2012
WebFOCUS 8.0.07
Windows, All Outputs
 
Posts: 59 | Registered: November 15, 2012Report This Post
Gold member
posted Hide Post
FrankDutch,

Thanks for the multiverb example. I will have to play around with that.

Daniel


In Focus since 2012
WebFOCUS 8.0.07
Windows, All Outputs
 
Posts: 59 | Registered: November 15, 2012Report This Post
Virtuoso
posted Hide Post
Multi-verb request example using the IBI GGSALES file. Might work for you, but depends on column title and formatting requirements.

DEFINE FILE GGSALES
 SALES_DATE/YYMD = DATE ;
 SALES_MONTH/Mt  = SALES_DATE ;
 SALES_YEAR/YY   = SALES_DATE ;
 Q1_SUM/D8       = IF (SALES_MONTH FROM 1  TO 3)  THEN UNITS ELSE 0 ;
 Q2_SUM/D8       = IF (SALES_MONTH FROM 4  TO 6)  THEN UNITS ELSE 0 ;
 Q3_SUM/D8       = IF (SALES_MONTH FROM 7  TO 9)  THEN UNITS ELSE 0 ;
 Q4_SUM/D8       = IF (SALES_MONTH FROM 10 TO 12) THEN UNITS ELSE 0 ;
END
-*
TABLE FILE GGSALES
 SUM
  Q1_SUM NOPRINT
  Q2_SUM NOPRINT
  Q3_SUM NOPRINT
  Q4_SUM NOPRINT
 BY SALES_YEAR      AS ''
-*
 SUM UNITS/D8       AS ''
 ACROSS SALES_MONTH AS ''
 COMPUTE Q1_SUMC/D8 = Q1_SUM ; AS 'Q1_Sum'
 COMPUTE Q2_SUMC/D8 = Q2_SUM ; AS 'Q2_Sum'
 COMPUTE Q3_SUMC/D8 = Q3_SUM ; AS 'Q3_Sum'
 COMPUTE Q4_SUMC/D8 = Q4_SUM ; AS 'Q4_Sum'
 BY SALES_YEAR      AS ''
END

This message has been edited. Last edited by: Dan Satchell,


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Gold member
posted Hide Post
Dan,

All I have to say is GREAT post! That worked much better than the solution that I came up with and it runs faster. You're a life saver. Thanks again!

Daniel


In Focus since 2012
WebFOCUS 8.0.07
Windows, All Outputs
 
Posts: 59 | Registered: November 15, 2012Report 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] Quarter totals at the end of a row

Copyright © 1996-2020 Information Builders