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,

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

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

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.

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.

Accros quarter
Accros month

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

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

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

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?

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.

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

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

FrankDutch,

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

Daniel

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,

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