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

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

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.

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

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 )

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

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

I'll put in seperate post.