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     Odd Data Setup -- Pull last 3 periods [SOLVED]

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Odd Data Setup -- Pull last 3 periods [SOLVED]
 Login/Join
 
Gold member
posted
All,

I was looking for some type of tecnique for a data pull. Basically I'm reading a file to get what our current period is.

To get our period number I can go to this file, read it and subtract one.

  
SET ASNAMES = ON
-* File Build_E3history_2.fex

TABLE FILE E3COMP
BY  LOWEST E3COMP.E3COMP.CPER13 AS 'CUR_CHECK'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE SAVE AS CUR_CHECK
END

-RUN
-READ CUR_CHECK &CUR_CHECK.A3.
-*-SET &CUR_CHECK=1;
-SET &CUR_PERIOD=IF &CUR_CHECK EQ 1 THEN 13 ELSE (&CUR_CHECK - 1);
-TYPE &CUR_CHECK
-TYPE &CUR_PERIOD



The next step would be the actual data pull, our data looks like the following:

Google Doc Example

So if we are in period 1 of 2013, we would need period 13 and 12 of 2012 along with period 1 of 2013.

Our Final result would be something like:

         ThisPeriod   LastPeriod    TwoPeriod
Item
WHSE  


Thank you any help is appreciated. I'll also be banging my head against the wall trying to get this to work!

Thank you,

JC

This message has been edited. Last edited by: John C.,


WF 7703 Outputs all
Windows 7 32
DB2 CLI
 
Posts: 59 | Registered: August 09, 2012Report This Post
Master
posted Hide Post
Been there, done that.

RELATIVE_PERIOD/I5 = ( YEAR * 13 ) + PERIOD;

This will create nice figure to work with.

P1 2013 will be = (2013*13)+1 = 26170
P13 2012 will be = (2012*13)+13 = 26169
P12 2012 will be = (2012*13)+12 = 26168

Now use your query to find out the 'cur_check'.

Today is P12 2013 = 26181
-SET &RELATIVE_CURCHECK = 26181;


Now query
TABLE FILE ...
SUM ...
BY  RELATIVE_PERIODE 
BY  YEAR
BY  PERIODE
WHERE RELATIVE_PERIODE LE &RELATIVE_CURCHECK
WHERE RELATIVE_PERIODE GE &RELATIVE_CURCHECK-2
END


Added bonus: You can use RELATIVE_PERIODE to sort the data if necessary...

Good luck,
Dave


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
Virtuoso
posted Hide Post
With something like this you wouldn't need to separately determine the current period:
TABLE FILE GGSALES
SUM MAX.DATE

SUM DOLLARS
BY HIGHEST 4 DATE
WHERE TOTAL MAX.DATE GT DATE;
END


This will automatically select the highest 3 dates in GGSALES that are before the latest date in that file. If you turn your periods + years into (chronological) date-like values - for example how Dave showed - you could use those to do the same.


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 :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Gold member
posted Hide Post
quote:
Originally posted by Dave:
Been there, done that.

RELATIVE_PERIOD/I5 = ( YEAR * 13 ) + PERIOD;

This will create nice figure to work with.

P1 2013 will be = (2013*13)+1 = 26170
P13 2012 will be = (2012*13)+13 = 26169
P12 2012 will be = (2012*13)+12 = 26168

Now use your query to find out the 'cur_check'.

Today is P12 2013 = 26181
-SET &RELATIVE_CURCHECK = 26181;


Now query
TABLE FILE ...
SUM ...
BY  RELATIVE_PERIODE 
BY  YEAR
BY  PERIODE
WHERE RELATIVE_PERIODE LE &RELATIVE_CURCHECK
WHERE RELATIVE_PERIODE GE &RELATIVE_CURCHECK-2
END


Added bonus: You can use RELATIVE_PERIODE to sort the data if necessary...

Good luck,
Dave


Dave,

Thank you for the great reply! With the data setup of:

  
Item ID	WHSE Year   P1_D P2_D P3_D    
2288210	2    2011	   775  746  372
2288210	2    2012	   646  855  419
2288210	2    2013   28   832  257
2288210	3    2011	   406  32   422
2288210	3    2012	   473  383  925


So if I'm in P4 it would be

TABLE FILE ...
SUM
P1_D AS 'L3-PERIOD'
P2_D AS 'L2-PERIOD'
P3_D AS 'L-PERIOD'
BY ITEM
BY WHSE
END

If it is Period 2 it would be


TABLE FILE ...
SUM
P12_D AS 'L3-PERIOD' (2012 ONLY)
P13_D AS 'L2-PERIOD' (2012 ONLY)
P1_D AS 'L-PERIOD' (2013)
BY ITEM
BY WHSE
END


I'm trying to understand how I translate my column P1 to the relative date? Do I use a define to map out the columns based on the relative date that I've created?

I understand this method if the periods were going down vs. accross. I think I need to dynamically select what columns to sum by year?

I'm trying to understand everything that I can to improve!

Thanks again!

John

This message has been edited. Last edited by: John C.,


WF 7703 Outputs all
Windows 7 32
DB2 CLI
 
Posts: 59 | Registered: August 09, 2012Report This Post
Master
posted Hide Post
John,

you don't have to...

TABLE FILE ...
SUM whatever
BY ITEM
BY WHSE
ACROSS P_RELATIVE NOPRINT
ACROSS P
END

Show do fine...

the
"ACROSS P_RELATIVE NOPRINT" is for sorting purposes only.

you could also do

ACROSS YEAR
ACROSS P

to achieve that.

( If I understand correctly what you want )


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
Platinum Member
posted Hide Post
Hi,

Based on what the data looked like in your spreadsheet, I would suggest that you DEFINE the three fields.

There are probably various ways to do this.

The example below has a few "redundant" steps e.g. setting &SEL_PER1 and &SEL_YEAR1, but hopefully it gives you some ideas that you could use.

-SET &CUR_CHECK  = '1' ;
-********
-SET &CUR_PERIOD = IF &CUR_CHECK EQ 1 THEN 13 ELSE (&CUR_CHECK - 1);
-* I don't know how you determine the year, so I've just hardcoded it for now
-SET &CUR_YEAR   = '2013' ;

-* Determine the YEAR for Two_Period(3), Last_Period(2) and ThisPeriod(1)
-SET &SEL_YEAR3  = IF &CUR_PERIOD LE 2 THEN (&CUR_YEAR - 1) ELSE &CUR_YEAR ;
-SET &SEL_YEAR2  = IF &CUR_PERIOD EQ 1 THEN (&CUR_YEAR - 1) ELSE &CUR_YEAR ;
-SET &SEL_YEAR1  = &CUR_YEAR ;

-* Determine the PERIOD for Two_Period(3), Last_Period(2) and ThisPeriod(1)
-SET &SEL_PER3   = IF &CUR_PERIOD EQ 1 THEN 12 ELSE  
-                  IF &CUR_PERIOD EQ 2 THEN 13 ELSE (&CUR_PERIOD - 1) ; 
-SET &SEL_PER2   = IF &CUR_PERIOD EQ 1 THEN 13 ELSE (&CUR_PERIOD - 1) ;
-SET &SEL_PER1   = &CUR_PERIOD ;

-* DEFINE your three fields using the variables above
-* NB: If Year is an Alphanumeric format (e.g. A4), then use .QUOTEDSTRING e.g. &SEL_YEAR1.QUOTEDSTRING
-* The applicable period field will be determine when WebFOCUS substitutes the amper variables
-* e.g. if &SEL_PER1 = 1, then P&SEL_PER1|_DEMAND will result in the fieldname P1_DEMAND

DEFINE ...
ThisPeriod/I9    = IF Year EQ &SEL_YEAR1 THEN P&SEL_PER1|_DEMAND ELSE 0 ;
Last_Period/I9   = IF Year EQ &SEL_YEAR2 THEN P&SEL_PER2|_DEMAND ELSE 0 ;
Two_Period/I9    = IF Year EQ &SEL_YEAR3 THEN P&SEL_PER3|_DEMAND ELSE 0 ;
END

TABLE FILE ...
-* I'd suggest having a filter to limit the retrieval to at most two years
-* If the three periods all fall in the same year it won't be a problem
IF Year EQ &SEL_YEAR1 OR &SEL_YEAR3

-* You will need to SUM your fields, so that it the data spans two years, it still only shows one line
SUM 
   ThisPeriod
   Last_Period
   Two_Period

BY Item_ID	
BY Warehouse_ID
END


WebFOCUS 8.2.06 mostly Windows Server
 
Posts: 195 | Location: Johannesburg, South Africa | Registered: September 13, 2008Report This Post
Gold member
posted Hide Post
All,

We sat for a long time friday afternoon and hashed through this. Wanted to thank you all for your input. This is our period pull:

(I'm sure there are other JDA/E3 customers that use WebFOCUS, would love to share ideas).

We also built a weekly file, becuase as soon as we normalized this file, we are sure they will ask for weekly data vs. period.

Here is the 3 last period pull:

  
-SET &CUR_CHECK=3;


-SET &L1_PERIOD=IF &CUR_CHECK EQ 1 THEN 13 ELSE (&CUR_CHECK - 1);
-SET &L1_PERIOD_YEAR=IF (&CUR_CHECK EQ 1) THEN &LASTYEAR ELSE &CURRENTYEAR;
-SET &L1P='P'| &L1_PERIOD |'_DEMAND';

-SET &L2_PERIOD=IF &L1_PERIOD EQ 1 THEN 13 ELSE IF &L1_PERIOD EQ 2 THEN 1 ELSE (&L1_PERIOD - 1);
-SET &L2_PERIOD_YEAR=IF &L1_PERIOD EQ 1 THEN &LASTYEAR ELSE IF &L1_PERIOD EQ 13 THEN &LASTYEAR ELSE &CURRENTYEAR;
-SET &L2P='P'| &L2_PERIOD |'_DEMAND';

-SET &L3_PERIOD=IF &L2_PERIOD EQ 13 THEN 12 ELSE IF &L2_PERIOD EQ 12 THEN 11 ELSE IF &L2_PERIOD EQ 1 THEN 13 ELSE (&L2_PERIOD - 1);
-SET &L3_PERIOD_YEAR=IF &L2_PERIOD EQ 12 THEN &LASTYEAR ELSE IF &L2_PERIOD EQ 13 THEN &LASTYEAR ELSE IF &L2_PERIOD EQ 1 THEN &LASTYEAR ELSE &CURRENTYEAR;
-SET &L3P='P'| &L3_PERIOD |'_DEMAND';

-TYPE &L1_PERIOD
-TYPE &L1_PERIOD_YEAR
-TYPE &L2_PERIOD
-TYPE &L2_PERIOD_YEAR
-TYPE &L3_PERIOD
-TYPE &L3_PERIOD_YEAR
-TYPE &L1P
-TYPE &L2P
-TYPE &L3P


DEFINE FILE E3HISTORY
DEMAND1/D12=IF HAYEAR EQ &L1_PERIOD_YEAR THEN &L1P ELSE 0;
DEMAND2/D12=IF HAYEAR EQ &L2_PERIOD_YEAR THEN &L2P ELSE 0;
DEMAND3/D12=IF HAYEAR EQ &L3_PERIOD_YEAR THEN &L3P ELSE 0;
END

TABLE FILE E3HISTORY
SUM
     E3HISTORY.SEG01.DEMAND1 AS 'DEMAND1'
     E3HISTORY.SEG01.DEMAND2 AS 'DEMAND2'
     E3HISTORY.SEG01.DEMAND3 AS 'DEMAND3'
	 COMPUTE DEMAND_MAX/D12=MAX(DEMAND1, DEMAND2, DEMAND3);
	 COMPUTE DEMAND_MIN/D12=MIN(DEMAND1, DEMAND2, DEMAND3);
	 COMPUTE USER_ID/A15='&USERID1';
	 COMPUTE PROG/A50='WF_Build_Forecast_Accuracy'; AS 'PROG_ID'
	 COMPUTE UPDATE_DATE/A8YYMD='&YYMD.EVAL'; AS 'UPDATE_DATE'
	 COMPUTE UPDATE_TIME/A8='&TIME';
BY  E3HISTORY.SEG01.HAITEM
BY  E3HISTORY.SEG01.HAWHSE
WHERE HAITEM EQ '2288210';
ON TABLE HOLD AS APP/E3_FORECAST_ACCURACY FORMAT DB2 TABLENAME DB2LIB/E3FCA
END

This message has been edited. Last edited by: John C.,


WF 7703 Outputs all
Windows 7 32
DB2 CLI
 
Posts: 59 | Registered: August 09, 2012Report This Post
Gold member
posted Hide Post
I'll put in seperate post.


WF 7703 Outputs all
Windows 7 32
DB2 CLI
 
Posts: 59 | Registered: August 09, 2012Report 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     Odd Data Setup -- Pull last 3 periods [SOLVED]

Copyright © 1996-2020 Information Builders