TIAThis message has been edited. Last edited by: <Emily McAllister>,
September 23, 2016, 07:09 AM
MartinY
Could be one way to do it
DEFINE FILE GGSALES
DTEYM /YYM = DATE;
END
TABLE FILE GGSALES
SUM UNITS
BY DTEYM
BY TOTAL COMPUTE ID /P3 = IF DTEYM EQ LAST DTEYM THEN ID ELSE ID + 1;
BY STCD
ON TABLE HOLD AS TMP FORMAT FOCUS
END
-RUN
TABLE FILE TMP
SUM UNITS
BY STCD
ACROSS ID
ACROSS DTEYM AS ''
END
-RUN
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
September 23, 2016, 09:03 AM
Wep5622
Sounds like a candidate for the McGyver technique.
Or, if you're reporting on an RDBMS that supports recursive CTE's or that has generator functions (or both), you could do it in SQL (using SQL passthru).
Especially the CTE's are fun queries to write, but you need to get your head in the right mode to understand how that recursion works.
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :
September 23, 2016, 09:22 AM
George Patton
How about using RANKED BY Start Date?
Run the request and hold the result and you will have a new field called RANK that you can subsequently use as you please.
TABLE FILE SWI SUM VOLUME BY TIME_TIME_HIERARCHY_WEEK WHERE TIME_TIME_HIERARCHY_WEEK GE '&START_DATE'; WHERE TIME_TIME_HIERARCHY_WEEK LE '&END_DATE'; ON TABLE HOLD END
DEFINE FILE HOLD NEWSORT/I1=IF TIME_TIME_HIERARCHY_WEEK NE LAST TIME_TIME_HIERARCHY_WEEK THEN LAST NEWSORT + 1 ELSE 1; END
TABLE FILE HOLD SUM VOLUME ACROSS NEWSORT END
WebFOCUS 7.6 Windows, All Outputs
September 23, 2016, 02:31 PM
Joe M
Thank You for everyone's reply...the stakeholder dropped the requirement I needed the counter for from the report.