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>getting previous month data in one pass

Read-Only Read-Only Topic
Go
Search
Notify
Tools
<solved>getting previous month data in one pass
 Login/Join
 
MonkeyP
Silver Member
posted
Going back to the basics, I am sure. Using the car file...I want a report that is This Months Sales and Previous Months sales by Country by Car.
Can I do this in one pass of the data, or do I have to create two hold files and bring them together.
I got SO involved in language translations coding I can't get my arms around this one either. Or it may be all the rain and lack of sun here in Atlanta.

Thanks

Wendy

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


release 7.7.03M
OS - Linux
HTML, PDF, Excel, Delimited files, Graphs, Dashboards, MRE, Report Caster
 
Posts: 35 | Registered: December 13, 2011Report This Post
Rao D
Platinum Member
posted Hide Post
Create new date variable like BegofPrevMon, EndofPrevMon, BegofCurMon, EndofCurMon.

Now in your Define statment issue something like
prev_sales/D20 = if (sale_date GE BegofPrevMon and sales_date LT EndofPrevMon) then SALES else 0 ;

Cur_sales/D20 = if (sale_date GE BegofCurMon and sales_date LT EndofCurMon) then SALES else 0 ;

In the report use SUM verb.


WebFOCUS - ver8201
[ReportingServers: Windows 64bit;
Client: tomcat and IIS on windows 2012
AppStudio

 
Posts: 104 | Location: Indianapolis | Registered: November 08, 2007Report This Post
njsden
Virtuoso
posted Hide Post
This example illustrates Rao's idea:

-DEFAULT &PM_FROM = 19970101;
-DEFAULT &PM_TO   = 19970131;
-DEFAULT &CM_FROM = 19970201;
-DEFAULT &CM_TO   = 19970228;

DEFINE FILE GGSALES
PM_SALES/D12 = IF GGSALES.SALES01.DATE FROM &PM_FROM TO &PM_TO THEN GGSALES.SALES01.UNITS ELSE 0;
CM_SALES/D12 = IF GGSALES.SALES01.DATE FROM &CM_FROM TO &CM_TO THEN GGSALES.SALES01.UNITS ELSE 0;
END

TABLE FILE GGSALES
SUM 
     PM_SALES AS 'Prev. Month,Sales'
     CM_SALES AS 'Curr. Month,Sales'
BY  LOWEST GGSALES.SALES01.REGION
WHERE ( GGSALES.SALES01.DATE GE &PM_FROM ) AND ( GGSALES.SALES01.DATE LE &CM_TO );
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
END


Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Tom Flynn
Expert
posted Hide Post
Why do people hard-code? Dates change, daily. I know, I've seen them do it!!! Big Grin

Many ways to do it dynamically, here's 1:
  
-SET &CUR_YR   = &DATEYY;
-SET &CUR_MO   = EDIT(&YYMD,'$$$$99');
-SET &PREV_YR  = IF &CUR_MO  EQ '01' THEN &CUR_YR - 1    ELSE &CUR_YR;
-SET &PREV_MO  = IF &CUR_MO  EQ '01' THEN '12'           ELSE &CUR_MO - 1;
-SET &PREV_MO  = IF &PREV_MO LT 10   THEN '0' | &PREV_MO ELSE &PREV_MO;
-SET &PREV_YYM = &PREV_YR | &PREV_MO;
-TYPE &PREV_YYM

This message has been edited. Last edited by: Tom Flynn,


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
njsden
Virtuoso
posted Hide Post
GGSALES contains static data, it does not change daily as real sources do. I want to provide a quick example that runs as is and hard-coding the values to match those of GGSALES was the fastest way to get to it.

Thanks for teaching us how to dynamically calculate dates though.


Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
MonkeyP
Silver Member
posted Hide Post
Thanks everyone! I think the dynamic solution fits best since our data is updated everyday but the report timeframe is always this month and last month..sometimes this month, last month, and month before that.

Worked like a charm!

Wendy


release 7.7.03M
OS - Linux
HTML, PDF, Excel, Delimited files, Graphs, Dashboards, MRE, Report Caster
 
Posts: 35 | Registered: December 13, 2011Report 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>getting previous month data in one pass

Copyright © 1996-2020 Information Builders