Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Across Measure then Across Years - InfoAssist
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Across Measure then Across Years - InfoAssist
 Login/Join
 
Member
posted
Hi Everyone,
I need to group by the measure, then across by year… that way we can easily see year over year comparisons on the same measure. Do you know how I could pull that off?

Currently I am summing my measures, then across fiscal years. That makes it read like this:

2016 2017 2018
group1 M1, M2, M3 M1, M2, M3 M1, M2, M3
group2 M1, M2, M3 M1, M2, M3 M1, M2, M3

I need it to read like this:

M1 TITLE M2 TITLE M3 TITLE
2016 2017 2018 2016 2017 2018 2016 2017 2018
group1 (M1) (M1) (M1) (M2) (M2) (M2) (M3) (M3) (M3)
group2 (M1) (M1) (M1) (M2) (M2) (M2) (M3) (M3) (M3)

Any ideas? I am working in Info

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 8.2, Server 2008 R2, SQL 2014 data warehouse
 
Posts: 14 | Location: Daytona Beach | Registered: November 19, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Hi Rosie,

First when posting sample use the code tag, it will keep it aligned. Last one on the ribbon
</>


Something such as this :
DEFINE FILE GGSALES
YR  /YY = DATE;
MTH /Mt = DATE;
END
TABLE FILE GGSALES
SUM DOLLARS
BY REGION AS ''
ACROSS MTH AS ''
ACROSS YR AS ''
ON TABLE SET PAGE-NUM NOLEAD
END
-RUN


WF 7.7.05M, 8.1.05M, 8.2.0.1M gen240 Windows, HTML, Excel, PDF
In Focus since 2007
 
Posts: 1791 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Virtuoso
posted Hide Post
I rarely ever use ACROSS for a measure because there could be an infinite number of values in a measure. I think what you're trying to do is to bin the numbers so they fall into specific ranges. For instance, instead of ACROSS AGE you could go ACROSS AGE_RANGE so instead of 100's of columns you end up with 0 to 10, 11 to 20 et cetera. In InfoAssist you have the GROUP and BIN functions when you right click on the column name. It'll automatically create these types of ranges for you.

But like MartinY says, we need a better description/depiction of your requirement to give you an answer. Perhaps you could give us an example using a sample file so we can apply the suggestion to a common data set.


WebFOCUS 8203, Unix, Windows
 
Posts: 1294 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Member
posted Hide Post
Thanks for looking guys! Sorry if my question wasn't clear.

So for example if I wanted to look at Total sales, total profits, and quantity sold
by Dealership Across years.
This is what I would like:
 
Dealership1 SalesYr1, SalesYr2, SalesYr3 | ProfitsYr1, ProfitsYr2, ProfitsYr3 | QuantityYr1, QuantityYr2, QuantityYr3
Dealership2 SalesYr1, SalesYr2, SalesYr3 | ProfitsYr1, ProfitsYr2, ProfitsYr3 | QuantityYr1, QuantityYr2, QuantityYr3
Dealership3 SalesYr1, SalesYr2, SalesYr3 | ProfitsYr1, ProfitsYr2, ProfitsYr3 | QuantityYr1, QuantityYr2, QuantityYr3
Dealership4 SalesYr1, SalesYr2, SalesYr3 | ProfitsYr1, ProfitsYr2, ProfitsYr3 | QuantityYr1, QuantityYr2, QuantityYr3
Dealership5 SalesYr1, SalesYr2, SalesYr3 | ProfitsYr1, ProfitsYr2, ProfitsYr3 | QuantityYr1, QuantityYr2, QuantityYr3


This format makes it easy to see how each dealership is performing in the different measures year over year. Does that make sense?

Here is my code on my data but it demonstrates the layout:
 
TABLE FILE expanse/v_los_collaborative
SUM V_LOS_COLLABORATIVE.V_LOS_COLLABORATIVE.VISITCOUNT AS 'CASES'
COMPUTE CMI;
AVE.V_LOS_COLLABORATIVE.V_LOS_COLLABORATIVE.LENGTHOFSTAYDAYS_DECIMAL AS 'ALOS'
BY V_LOS_COLLABORATIVE.V_LOS_COLLABORATIVE.EPSI_PAYOR
ACROSS V_LOS_COLLABORATIVE.V_LOS_COLLABORATIVE.FISCALYEAR AS 'CASES'
WHERE DISCHARGEMONTH EQ LASTMONTHMONTH;
 


It Prints in this format:
  
Dealership1 SalesYr1, ProfitsYr1, QuantityYr1 | SalesYr2, ProfitsYr2, QuantityYr2 | SalesYr3, ProfitsYr3 , QuantityYr3
Dealership2 SalesYr1, ProfitsYr1, QuantityYr1 | SalesYr2, ProfitsYr2, QuantityYr2 | SalesYr3, ProfitsYr3 , QuantityYr3
Dealership3 SalesYr1, ProfitsYr1, QuantityYr1 | SalesYr2, ProfitsYr2, QuantityYr2 | SalesYr3, ProfitsYr3 , QuantityYr3
Dealership4 SalesYr1, ProfitsYr1, QuantityYr1 | SalesYr2, ProfitsYr2, QuantityYr2 | SalesYr3, ProfitsYr3 , QuantityYr3


WebFOCUS 8.2, Server 2008 R2, SQL 2014 data warehouse
 
Posts: 14 | Location: Daytona Beach | Registered: November 19, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Is this may be an option ?
DEFINE FILE GGSALES
YR  /YY = DATE;
END
TABLE FILE GGSALES
SUM DOLLARS OVER
    BUDDOLLARS OVER
    UNITS OVER
    BUDUNITS
BY REGION AS ''
ACROSS YR AS ''
ON TABLE SET PAGE-NUM NOLEAD
END
-RUN


Or from a manual way
DEFINE FILE GGSALES
YR  /YY = DATE;
END
TABLE FILE GGSALES
SUM DOLLARS/P8 AS 'VAL'
BY REGION
BY YR
BY TOTAL COMPUTE COLID   /I2 = 1;
BY TOTAL COMPUTE COLTITL /A10V = 'SALES';
ON TABLE HOLD AS TMP1
END
-RUN

TABLE FILE GGSALES
SUM BUDDOLLARS/P8 AS 'VAL'
BY REGION
BY YR
BY TOTAL COMPUTE COLID   /I2 = 2;
BY TOTAL COMPUTE COLTITL /A10V = 'BDG SALES';
ON TABLE HOLD AS TMP2
END
-RUN

TABLE FILE GGSALES
SUM UNITS/P8 AS 'VAL'
BY REGION
BY YR
BY TOTAL COMPUTE COLID    /I2 = 3;
BY TOTAL COMPUTE COLTITL /A10V = 'UNITS';
ON TABLE HOLD AS TMP3
END
-RUN

TABLE FILE GGSALES
SUM BUDUNITS/P8 AS 'VAL'
BY REGION
BY YR
BY TOTAL COMPUTE COLID   /I2 = 4;
BY TOTAL COMPUTE COLTITL /A10V = ' BDG UNITS';
ON TABLE HOLD AS TMP4
END
-RUN

TABLE FILE TMP1
SUM VAL
BY REGION
BY YR
BY COLID
BY COLTITL
ON TABLE HOLD AS RPTDATA
MORE
FILE TMP2
MORE
FILE TMP3
MORE
FILE TMP4
END
-RUN

TABLE FILE RPTDATA
SUM VAL AS ''
BY REGION AS ''
ACROSS COLID NOPRINT
ACROSS COLTITL AS ''
ACROSS YR AS ''
ON TABLE SET PAGE-NUM NOLEAD
END
-RUN


WF 7.7.05M, 8.1.05M, 8.2.0.1M gen240 Windows, HTML, Excel, PDF
In Focus since 2007
 
Posts: 1791 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Virtuoso
posted Hide Post
How about using the "Alternate Master" technique:
  
-SET &ECHO=ALL;
-* File rosie01.fex
TABLE FILE CAR
-* SALES, RETAIL and DEALER should have the same format!
SUM SALES/D7 RETAIL_COST DEALER_COST
BY COUNTRY
BY SEATS
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS ROSIE
END
-RUN
-* Alternate Master: use -LINES internal procedure to write in memory
-* All mesures are "collapsed" into one field: VALUE
-* The virtual field VAL is an index: 1-SALES, 2-RETAIL, 3-DEALER
EX -LINES 8 EDAPUT MASTER,ROSIE,C,MEM
 FILENAME=ROSIE   , SUFFIX=FIX
 SEGMENT=ROSIE, SEGTYPE=S2
 FIELDNAME=COUNTRY, ALIAS=E01, USAGE=A10, ACTUAL=A12, $
 FIELDNAME=SEATS, ALIAS=E02, USAGE=I3, ACTUAL=I04, $
 SEGMENT=VALS, PARENT=ROSIE, OCCURS=VARIABLE
 FIELDNAME=VALUE, ALIAS=E03, USAGE=D7, ACTUAL=D08, $
 FIELDNAME=VAL, ALIAS=ORDER, USAGE=I2, ACTUAL=I4, $
-RUN
-* Use the virtual field VAL to produce titles in the report
DEFINE FILE ROSIE
FIELDV/A6 =DECODE VAL(1 'SALES' 2 'RETAIL' 3 'DEALER');
TITLEV/A7 = FIELDV || EDIT(SEATS,'$$9');
END
TABLE FILE ROSIE
SUM VALUE
BY COUNTRY
ACROSS VAL NOPRINT
ACROSS SEATS NOPRINT
ACROSS TITLEV
ON TABLE SET ACROSSTITLE SIDE
END


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

 
Posts: 1885 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Across Measure then Across Years - InfoAssist

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.