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     Position of Values in Columns or Across

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Position of Values in Columns or Across
 Login/Join
 
RyanIPG13
Silver Member
posted
Hi everyone,

Running into a problem I can't see to overcome at the moment. I am trying to re-create a user report in Excel that has a very specific grouping of columns.

Right now the report looks something like this:

   
Units					       Revenue
2016				2017	2018 | 2016				2017	2018
Q1	Q2	Q3	Q4		     | Q1	Q2	Q3	Q4
1 2 3 	4 5 6	7 8 9	10 11 12	     | 1 2 3 	4 5 6	7 8 9	10 11 12


As you can see the units and revenue are two complete separate areas and below they group the information into years, quarters, periods, etc. Each year has a total of all quarters and each quarter has a total of all periods.

The best I can get in WebFocus is something like this (reduced data size due to length):

2016
Q1
1 2 3 4
Units Revenue Units Revenue Units Revenue Units Revenue

As you can see the units and revenue repeat for each section at the lowest level due to how measures follow across' for example. I know no other way to break everything apart then to due a hold file for each year and try to shove them together. Am I missing something or is there a better way to do this?

Thanks.

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


Production: WebFOCUS 8.202M
QA: WebFOCUS 8.206.01
Windows/SQL
 
Posts: 37 | Registered: May 24, 2017Report This Post
MartinY
Virtuoso
posted Hide Post
Hi Ryan,

First, when you post sample, use the code tag and put it in between. It will give you that ability to preserve the positioning.
It's the last icon on the ribbon 
</>


Then, I'm not sure of what you want. How can you have 2016, 2017 & 2018 within the same Q1, Q2, Q3, Q4 period ?

Is it something more like this that you want or similar to this ? (don't mind about "Year :", "Quarter :", "Month :", "Rev Unit:", ... I put them just to identify what each row is)
          Units                                                                                                           | Revenus
Year    : 2016                                | 2017                                | 2018                                | 2016                                | 2017                                | 2018
Yr Tot  : xxxx                                | xxxx                                | xxxx                                | xxxx                                | xxxx                                | xxxx                                
Quarter : Q1       Q2       Q3       Q4       | Q1       Q2       Q3       Q4       | Q1       Q2       Q3       Q4       | Q1       Q2       Q3       Q4       | Q1       Q2       Q3       Q4       | Q1       Q2       Q3       Q4
Qtr Tot : xxx      xxx      xxx      xxx      | xxx      xxx      xxx      xxx      | xxx      xxx      xxx      xxx      | xxx      xxx      xxx      xxx      | xxx      xxx      xxx      xxx      | xxx      xxx      xxx      xxx
Month   : 1  2  3  4  5  6  7  8  9  10 11 12 | 1  2  3  4  5  6  7  8  9  10 11 12 | 1  2  3  4  5  6  7  8  9  10 11 12 | 1  2  3  4  5  6  7  8  9  10 11 12 | 1  2  3  4  5  6  7  8  9  10 11 12 | 1  2  3  4  5  6  7  8  9  10 11 12
Value   : x  x  x  x  x  x  x  x  x   x  x  x | x  x  x  x  x  x  x  x  x   x  x  x | x  x  x  x  x  x  x  x  x   x  x  x | x  x  x  x  x  x  x  x  x   x  x  x | x  x  x  x  x  x  x  x  x   x  x  x | x  x  x  x  x  x  x  x  x   x  x  x


If my assumption is good, does something such as this may suits your need ?
(Below is possible because both UNITS and DOLLARS have the same format, otherwise you just need to make them the same)
DEFINE FILE GGSALES
YR  /YY    = DATE;
MTH /MONTH = DATE;
QTR /A2    = DECODE MTH (1 'Q1' 2 'Q1' 3 'Q1' 4 'Q2' 5 'Q2' 6 'Q2' 7 'Q3' 8 'Q3' 9 'Q3' 10 'Q4' 11 'Q4' 12 'Q4');
END
TABLE FILE GGSALES
SUM UNITS AS 'VALUE'
-* If not same format dynamically assign same format
-*SUM UNITS/I8C AS 'VALUE'
BY TOTAL COMPUTE ID/I1 = 1;
BY REGION
BY YR
BY QTR
BY MTH
WHERE YR EQ 1996 OR 1997;
ON TABLE HOLD AS QTYDATA FORMAT FOCUS
END
-RUN

DEFINE FILE GGSALES
YR  /YY    = DATE;
MTH /MONTH = DATE;
QTR /A2    = DECODE MTH (1 'Q1' 2 'Q1' 3 'Q1' 4 'Q2' 5 'Q2' 6 'Q2' 7 'Q3' 8 'Q3' 9 'Q3' 10 'Q4' 11 'Q4' 12 'Q4');
END
TABLE FILE GGSALES
SUM DOLLARS AS 'VALUE'
-* If not same format dynamically assign same format
-*SUM DOLLARS/I8C AS 'VALUE'
BY TOTAL COMPUTE ID/I1 = 2;
BY REGION
BY YR
BY QTR
BY MTH
WHERE YR EQ 1996 OR 1997;
ON TABLE HOLD AS DOLDATA FORMAT FOCUS
END
-RUN

TABLE FILE QTYDATA
SUM VALUE
BY ID
BY REGION
BY YR
BY QTR
BY MTH
ON TABLE HOLD AS RPTDATA FORMAT FOCUS
MORE
FILE DOLDATA
END
-RUN

DEFINE FILE RPTDATA
IDX /A10    = DECODE ID (1 'Units' 2 'Revenue');
END
TABLE FILE RPTDATA
SUM VALUE   AS ''
BY REGION   AS ''
ACROSS ID   NOPRINT
ACROSS IDX  AS ''
ACROSS YR   AS ''
ACROSS QTR  AS ''
ACROSS MTH  AS ''
ON TABLE SET PAGE-NUM NOLEAD
END
-RUN

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


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
RyanIPG13
Silver Member
posted Hide Post
Hi Martin,

Thanks very much for the extremely fast reply!

You have the formatting correct based on my original post, I adjusted my original post but what you put below is spot on with one minor thing I left out unfortunately.

The other complication is that all of those fields must be across fields because I have fields to the left of all the years.

So to add to your very nicely drawn diagram, this is truly what I'm looking for.

V are values, T totals (for quarters) and YT is a total of the year.

  							Units                                                                                                           		  | Revenue
							Year    : 2016                    	            						| 2017                                | 2018                                | 2016                                | 2017                                | 2018
							Quarter : Q1       		Q2       		Q3       		Q4       		| Q1       Q2       Q3       Q4       | Q1       Q2       Q3       Q4       | Q1       Q2       Q3       Q4       | Q1       Q2       Q3       Q4       | Q1       Q2       Q3       Q4
							Qtr Tot : xxx      		xxx      		xxx      		xxx      		| xxx      xxx      xxx      xxx      | xxx      xxx      xxx      xxx      | xxx      xxx      xxx      xxx      | xxx      xxx      xxx      xxx      | xxx      xxx      xxx      xxx
							Month   : 1  2  3  		4  5  6  		7  8  9  		10 11 12 		| 1  2  3  4  5  6  7  8  9  10 11 12 | 1  2  3  4  5  6  7  8  9  10 11 12 | 1  2  3  4  5  6  7  8  9  10 11 12 | 1  2  3  4  5  6  7  8  9  10 11 12 | 1  2  3  4  5  6  7  8  9  10 11 12
									: x  x  x  		x  x  x  		x  x  x   		x  x  x 		| x  x  x  x  x  x  x  x  x   x  x  x | x  x  x  x  x  x  x  x  x   x  x  x | x  x  x  x  x  x  x  x  x   x  x  x | x  x  x  x  x  x  x  x  x   x  x  x | x  x  x  x  x  x  x  x  x   x  x  x

Field 1								  V	 V	V	T	V	 V	V	T	V	 V	V	T	V  V  V	  T  YT
	Field2							  V	 V	V	T	V	 V	V	T	V	 V	V	T	V  V  V	  T  YT
		Field3						  V	 V	V	T	V	 V	V	T	V	 V	V	T	V  V  V	  T  YT
			Field4					  V	 V	V	T	V	 V	V	T	V	 V	V	T	V  V  V	  T  YT
				Field5				  V	 V	V	T	V	 V	V	T	V	 V	V	T	V  V  V	  T  YT
					Field6			  V	 V	V	T	V	 V	V	T	V	 V	V	T	V  V  V	  T  YT
												T				T 				T			  T	 YT



quote:
Originally posted by MartinY:
Hi Ryan,

First, when you post sample, use the code tag and put it in between. It will give you that ability to preserve the positioning.
It's the last icon on the ribbon 
</>


Then, I'm not sure of what you want. How can you have 2016, 2017 & 2018 within the same Q1, Q2, Q3, Q4 period ?

Is it something more like this that you want or similar to this ? (don't mind about "Year :", "Quarter :", "Month :", "Rev Unit:", ... I put them just to identify what each row is)
          Units                                                                                                           | Revenus
Year    : 2016                                | 2017                                | 2018                                | 2016                                | 2017                                | 2018
Yr Tot  : xxxx                                | xxxx                                | xxxx                                | xxxx                                | xxxx                                | xxxx                                
Quarter : Q1       Q2       Q3       Q4       | Q1       Q2       Q3       Q4       | Q1       Q2       Q3       Q4       | Q1       Q2       Q3       Q4       | Q1       Q2       Q3       Q4       | Q1       Q2       Q3       Q4
Qtr Tot : xxx      xxx      xxx      xxx      | xxx      xxx      xxx      xxx      | xxx      xxx      xxx      xxx      | xxx      xxx      xxx      xxx      | xxx      xxx      xxx      xxx      | xxx      xxx      xxx      xxx
Month   : 1  2  3  4  5  6  7  8  9  10 11 12 | 1  2  3  4  5  6  7  8  9  10 11 12 | 1  2  3  4  5  6  7  8  9  10 11 12 | 1  2  3  4  5  6  7  8  9  10 11 12 | 1  2  3  4  5  6  7  8  9  10 11 12 | 1  2  3  4  5  6  7  8  9  10 11 12
Value   : x  x  x  x  x  x  x  x  x   x  x  x | x  x  x  x  x  x  x  x  x   x  x  x | x  x  x  x  x  x  x  x  x   x  x  x | x  x  x  x  x  x  x  x  x   x  x  x | x  x  x  x  x  x  x  x  x   x  x  x | x  x  x  x  x  x  x  x  x   x  x  x


If my assumption is good, does something such as this may suits your need ?
DEFINE FILE GGSALES
YR  /YY    = DATE;
MTH /MONTH = DATE;
QTR /A2    = DECODE MTH (1 'Q1' 2 'Q1' 3 'Q1' 4 'Q2' 5 'Q2' 6 'Q2' 7 'Q3' 8 'Q3' 9 'Q3' 10 'Q4' 11 'Q4' 12 'Q4');
END
TABLE FILE GGSALES
SUM UNITS AS 'VALUE'
BY TOTAL COMPUTE ID/I1 = 1;
BY REGION
BY YR
BY QTR
BY MTH
WHERE YR EQ 1996 OR 1997;
ON TABLE HOLD AS QTYDATA FORMAT FOCUS
END
-RUN

DEFINE FILE GGSALES
YR  /YY    = DATE;
MTH /MONTH = DATE;
QTR /A2    = DECODE MTH (1 'Q1' 2 'Q1' 3 'Q1' 4 'Q2' 5 'Q2' 6 'Q2' 7 'Q3' 8 'Q3' 9 'Q3' 10 'Q4' 11 'Q4' 12 'Q4');
END
TABLE FILE GGSALES
SUM DOLLARS AS 'VALUE'
BY TOTAL COMPUTE ID/I1 = 2;
BY REGION
BY YR
BY QTR
BY MTH
WHERE YR EQ 1996 OR 1997;
ON TABLE HOLD AS DOLDATA FORMAT FOCUS
END
-RUN

TABLE FILE QTYDATA
SUM VALUE
BY ID
BY REGION
BY YR
BY QTR
BY MTH
ON TABLE HOLD AS RPTDATA FORMAT FOCUS
MORE
FILE DOLDATA
END
-RUN

DEFINE FILE RPTDATA
IDX /A10    = DECODE ID (1 'Units' 2 'Revenue');
END
TABLE FILE RPTDATA
SUM VALUE   AS ''
BY REGION   AS ''
ACROSS ID   NOPRINT
ACROSS IDX  AS ''
ACROSS YR   AS ''
ACROSS QTR  AS ''
ACROSS MTH  AS ''
ON TABLE SET PAGE-NUM NOLEAD
END
-RUN


Production: WebFOCUS 8.202M
QA: WebFOCUS 8.206.01
Windows/SQL
 
Posts: 37 | Registered: May 24, 2017Report This Post
RyanIPG13
Silver Member
posted Hide Post
Actually..I think your solution worked perfect Martin. I had to tweak one or two things based on the data on my end, but I never knew about the BY TOTAL COMPUTE that you included.

Thanks very much for the help!


Production: WebFOCUS 8.202M
QA: WebFOCUS 8.206.01
Windows/SQL
 
Posts: 37 | Registered: May 24, 2017Report This Post
MartinY
Virtuoso
posted Hide Post
Not exactly layout as you displayed, but using WF feature as is we can easily have totals like this
DEFINE FILE GGSALES
YR  /YY    = DATE;
MTH /MONTH = DATE;
QTR /A2    = DECODE MTH (1 'Q1' 2 'Q1' 3 'Q1' 4 'Q2' 5 'Q2' 6 'Q2' 7 'Q3' 8 'Q3' 9 'Q3' 10 'Q4' 11 'Q4' 12 'Q4');
END
TABLE FILE GGSALES
-*SUM UNITS AS 'VALUE'
-* If not same format dynamically assign same format
SUM UNITS/I8C AS 'VALUE'
BY TOTAL COMPUTE ID/I1 = 1;
BY REGION
BY YR
BY QTR
BY MTH
WHERE YR EQ 1996 OR 1997;
ON TABLE HOLD AS QTYDATA FORMAT FOCUS
END
-RUN

DEFINE FILE GGSALES
YR  /YY    = DATE;
MTH /MONTH = DATE;
QTR /A2    = DECODE MTH (1 'Q1' 2 'Q1' 3 'Q1' 4 'Q2' 5 'Q2' 6 'Q2' 7 'Q3' 8 'Q3' 9 'Q3' 10 'Q4' 11 'Q4' 12 'Q4');
END
TABLE FILE GGSALES
-*SUM DOLLARS AS 'VALUE'
-* If not same format dynamically assign same format
SUM DOLLARS/I8C AS 'VALUE'
BY TOTAL COMPUTE ID/I1 = 2;
BY REGION
BY YR
BY QTR
BY MTH
WHERE YR EQ 1996 OR 1997;
ON TABLE HOLD AS DOLDATA FORMAT FOCUS
END
-RUN

TABLE FILE QTYDATA
SUM VALUE
BY ID
BY REGION
BY YR
BY QTR
BY MTH
ON TABLE HOLD AS RPTDATA FORMAT FOCUS
MORE
FILE DOLDATA
END
-RUN

DEFINE FILE RPTDATA
IDX /A10    = DECODE ID (1 'Units' 2 'Revenue');
END
TABLE FILE RPTDATA
SUM VALUE   AS ''
BY REGION   AS ''
ACROSS ID   NOPRINT
ACROSS IDX  AS ''
ACROSS YR   AS ''
ACROSS QTR  AS ''
ACROSS-TOTAL AS 'Year Total'
ACROSS MTH  AS ''
ACROSS-TOTAL AS 'Qtr Total'

ON TABLE SUMMARIZE

ON TABLE SET PAGE-NUM NOLEAD
END
-RUN

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


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
RyanIPG13
Silver Member
posted Hide Post
Thanks again Martin!

I tried those totals a bunch of different ways, couldn't get them to line up.


Production: WebFOCUS 8.202M
QA: WebFOCUS 8.206.01
Windows/SQL
 
Posts: 37 | Registered: May 24, 2017Report This Post
MartinY
Virtuoso
posted Hide Post
quote:
I tried those totals a bunch of different ways, couldn't get them to line up.

What do you mean by the above ?

Using my last sample, it should give you something layout like this
        Units
        2016                                                                                 | 2017                                
        Q1                 Q2                 Q3                 Q4                 Yr Total | Q1                 Q2                 Q3                 Q4                 Yr Total
        1  2  3  Qtr Total 4  5  6  Qtr Total 7  8  9  Qtr Total 10 11 12 Qtr Total          | 1  2  3  Qtr Total 4  5  6  Qtr Total 7  8  9  Qtr Total 10 11 12 Qtr Total         
Region1 x  x  x  xxxxx     x  x  x  xxxxx     x  x  x  xxxxx      x  x  x xxxxx     xxxxxxx  | x  x  x  xxxxx     x  x  x  xxxxx     x  x  x  xxxxx      x  x  x xxxxx     xxxxxxx
Region2 x  x  x  xxxxx     x  x  x  xxxxx     x  x  x  xxxxx      x  x  x xxxxx     xxxxxxx  | x  x  x  xxxxx     x  x  x  xxxxx     x  x  x  xxxxx      x  x  x xxxxx     xxxxxxx
Region3 x  x  x  xxxxx     x  x  x  xxxxx     x  x  x  xxxxx      x  x  x xxxxx     xxxxxxx  | x  x  x  xxxxx     x  x  x  xxxxx     x  x  x  xxxxx      x  x  x xxxxx     xxxxxxx
Total   xx xx xx xxxxxxx   xx xx xx xxxxxxx   xx xx xx xxxxxxx   xx xx xx xxxxxxx   xxxxxxxx | xx xx xx xxxxxxx   xx xx xx xxxxxxx   xx xx xx xxxxxxx   xx xx xx xxxxxxx   xxxxxxxx


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report 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     Position of Values in Columns or Across

Copyright © 1996-2020 Information Builders