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     [CLOSED] How to get Previous Period

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] How to get Previous Period
 Login/Join
 
Guru
posted
I am writing a report, I have to show data for current period, and previouse three periods as follows:

Current Previous 1 Previous 2 previouse 3

I have a Time table I can pick periods from. How do you pick previous periods and how do you tell system whats current period?

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


WebFOCUS 7.6.10
Windows
HTML
 
Posts: 294 | Registered: March 04, 2010Report This Post
Expert
posted Hide Post
Arif, you really need to give us more information on your questions.

What constitutes a time period, month, quarter, what?

What is in the time period?

Is there anything in the data table that you can join or match to in the time period table?

What are the formats of these fields?

Please give us more to go on.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Guru
posted Hide Post
How about compare your period-end dates against(LT) the current date?


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
Expert
posted Hide Post
TABLE FILE CENTORD
SUM
QUANTITY
BY HIGHEST 4 ORDER_DATE

WHERE ORDER_DATE LE '20011228'
ON TABLE HOLD AS H001
END

TABLE FILE H001
SUM
QUANTITY
ACROSS HIGHEST ORDER_DATE
END



Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
quote:
quote:
TODAY/YYMD = ITODAY;

This is the formate of table
dim_Time_key FiscalYear Period Week
1 2004 01 1

Date CalendarYear FYPW FYP
2003-03-29 2003 2004011 200401

Date is dateformat, Period and week can be calculated from FYPW.
I am trying to get TODAY - 7 as current period because my data gets updated every 7 days, so previous week is my current period and week.

I tried follwing but it didnt work
Current = TODAY - 7
CURPERIOD = FYPW
WHERE Current = DATE

Orignal question was:
How do I get Current Period, Previous 1, Previous 2, Previous 3


Your period seems to be year and month from which you can subtract 3 to get the previous 3. How is week related to that? That should be the amper variable (e.g. &PERIOD_3) that you use in your WHERE clause. Do this part all in Dialogue Manager.

What you then need to do first is convert TODAY to your FYPW format. You can do that in a DEFINE. For instance:
DEFINE FILE CAR
CURRDATE/YYMD=&YYMD;
CURRFYP/YYM=CURRDATE;
CURRFYPI/I6YYM=CURRFYP;
CURRWK/W=CURRDATE;
CURRFYPW/A8=EDIT(CURRFYPI) || EDIT(CURRWK);
END
TABLE FILE CAR
PRINT CURRDATE CURRFYP CURRWK CURRFYPW
BY COUNTRY
IF READLIMIT EQ 1
END


I only used the CAR file as an example. After the date in the data file is reformatted, you can same something like this:
WHERE CURRFYPW GE &PERIOD_3;


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Silver Member
posted Hide Post
You Can Try below mentioned Functions:
DATEADD

or

DATEMOV

Try using them together as combination....

Say For example--- In my report I need to show Three Different Dates:

  CUR_DT/YYMD=&YYMD;
TO_DAY/wr =CUR_DT;
NBD_DT/YYMD=DATEMOV(CUR_DT, 'NBD');
PBD_DT/YYMD=DATEMOV(CUR_DT, 'PBD');
PPBD_DT/YYMD=IF TO_DAY EQ 'Tuesday' THEN DATEADD(CUR_DT,'D',-4) ELSE DATEADD(PBD_DT,'D',-1); 


Hope this will help.....


WebFOCUS 7.6.X/7.7.X
Windows
all output
 
Posts: 31 | Registered: March 17, 2010Report This Post
Expert
posted Hide Post
There is a lot of suggestions, but we still don't know what a period is, and how its stored.
Confused

Arif, if you want usable suggestions, please give more information.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Guru
posted Hide Post
quote:
Arif, if you want usable suggestions, please give more information.


dim_Time_key FiscalYear Period Week
2 2004 01 1

Date Ca lendarYear FYPW FYP Enro
2003-03-30 2003 2004011 200401 50

Above are DB fields:
Period is a field in my DATAbase. I have Fiscal periods. Current week is todays date - 7 days

I want to be able to accomplish following:
1. When user runs the report i want to sum enr number for current week and last weeks of previous 2 periods.
Example:
If i am running a reprot today i want week that ended on April 23rds numbers.
I also want Marchs last weeks number
I want Feb Last weeks numbers.

How do I get this accomplish. Makeing assumpion that Months are periods where periods are my Fiscal periods my fiscal year starts in July.

Arif


WebFOCUS 7.6.10
Windows
HTML
 
Posts: 294 | Registered: March 04, 2010Report This Post
Guru
posted Hide Post
dim_Time_key FiscalYear Period Week
2 2004 01 1

Date CalendarYear FYPW FYP Enro
2003-03-30 2003 2004011 200401 50


WebFOCUS 7.6.10
Windows
HTML
 
Posts: 294 | Registered: March 04, 2010Report This Post
Guru
posted Hide Post
DEFINE FILE PERIOD_FILE
CAL_DATE/I8 = EDIT(DATE,'9999$99$99');
END
TABLE FILE PERIOD_FILE
PRINT
Enro
BY HIGHEST 3 FYP NOPRINT
BY HIGHEST FYPW
WHERE CAL_DATE LE &YYMD
END

Will this give you the current month plus the previous 2 months periods? You have to work out the CAL_DATE from the db2 field(DATE) so it can be comparable to &YYMD.


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report 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     [CLOSED] How to get Previous Period

Copyright © 1996-2020 Information Builders