[CLOSED] How to get Previous Period

April 29, 2010, 09:01 AM

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,

April 29, 2010, 09:43 AM

GinnyJakes

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.

April 29, 2010, 10:19 AM

Hua

How about compare your period-end dates against(LT) the current date?

April 29, 2010, 10:34 AM

Francis Mariani

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

April 29, 2010, 11:13 AM

GinnyJakes

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;

April 29, 2010, 11:36 AM

webFocus_reporter

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.....

April 29, 2010, 05:40 PM

Waz

There is a lot of suggestions, but we still don't know what a period is, and how its stored.

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

April 30, 2010, 09:12 AM

Arif

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

April 30, 2010, 09:28 AM

Arif

dim_Time_key FiscalYear Period Week
2 2004 01 1

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

April 30, 2010, 01:36 PM

Hua

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.

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