Ok, I've stumped myself on this one. I will try to explain it best as I can. I need to create a report with current inventory and it's depletion over the next 12 weeks based on the last 8 weeks sales average. I don't have a problem working with the data and dates, the problem is I can't figure out how to dymanically calculate it.

example: (this is data I have)
inventory, week average, and current date
call them what you want (field1, field2, field3)

field1/field2/field3
2,000 | 125 | 02/21/2008

this is how i want to see it:

Date_________Inventory
02/21/2008 | 2,000
02/28/2008 | 1,875
03/06/2008 | 1,750
03/13/2008 | 1,625
and so on, preferably to 0


I'm pretty good with the dateadd/datecvt functions so calculating future dates is no problem. It just the layout that the problem.

Is it possible the "FORECAST Processing" functionality might help you? I've never used it, but it sounds like something you want to do.

Look for "FORECAST Processing" and "How to Calculate Trends and Predict Values" in the "Creating Reports With WebFOCUS Language" documentation.

I think you first have to calculate the step size (in your example 125)

Calculate the actual date minus 8 weeks.
Then the average over the last 8 weeks.
Calculate the step by dividing that average by 12.

Now I think you need the mcGuyver tech to get 12 rows and calculate the steps.

Frank is correct, MacGyver will work best as you have to generate 8 output lines for the single input row.

Since you already have the starting date and the average, you will calcuate the next date and inventory based on the value of the CNTR field in your FSEQ file. Of course, you need to read the doc on MacGyver first. Too much to put into the post.

JOIN BLANK WITH DATE TO BLANK IN FSEQ AS J1
DEFINE FILE filename
BLANK/A1 WITH DATE=' ';
DAYS_TO_ADD/I2=CNTR*7;
NEXT_WEEK/YYMD=DATE+DAYS_TO_ADD;
NEXT_INV/I6=IF CNTR EQ 1 THEN INV ELSE NEXT_INV - SALES_AVG;
END
TABLE FILE filename
PRINT NEXT_WEEK NEXT_INV
BY CNTR NOPRINT
WHERE CNTR LE 8
END  

You'll have to put your join in and create your FSEQ file first. Code not guaranteed but something to play with.

trob, i would cheat...
to get your 8 wks average..
i would create 2 extract files and join them
each would contain the cumulative total by week since time began
I would join them, having forced one of the files into the future by 8 weeks, by defining an 8-wk-later date.
calculate the difference between the 2 cumulatives, and divide by 8. bingo.
its MacGyver-for-dummies..
( so i guess thats what... david hasselhof?)
example:
so lets say you have a file starting with Jan 1 2007 with 100 units every week.
TABLE FILE ..
PRINT VALUE COMPUTE CUMULATIVE_VALUE= VALUE + LAST CUMULATIVE_VALUE ;
BY REAL_DATE
ON TABLE HOLD AS H1
END

..so we have...
real_date value	cumvalue
1-Nov	100   4400
1-Dec	100   4800
1-Jan	100   5200
1-Feb	100   5600
1-Mar	100   6000

DEFINE FILE H1
BASE_DATE/YYMD=REAL_DATE + 8 WKS;..however you like calculating that best.., REAL_DATE+56, or maybe an AYMD(.., 56,..)..however you like.
END
TABLE FILE H1
PRINT VALUE CUMULATIVE_VALUE AS CUMULATIVE_VALUEB
BY BASE_DATE BY REAL_DATE
ON TABLE HOLD AS H2
END
-RUN

so far:
base   real
date   date   value cumvalueB
1-Sep  1-Nov	100 4400
1-Oct  1-Dec	100 4800
1-Nov  1-Jan	100 5200
1-Dec  1-Feb	100 5600
1-Jan  1-Mar	100 6000

..now join them up
JOIN REAL_DATE AS BASE_DATE IN H1 TO BASE_DATE IN H2 AS ..
..so now we have

base	real	value	cumA	cumB
1-Sep	1-Nov	100	4400	3600
1-Oct	1-Dec	100	4800	4000
1-Nov	1-Jan	100	5200	4400
1-Dec	1-Feb	100	5600	4800
1-Jan	1-Mar	100	6000	5200
1-Feb	1-Apr	100	6400	5600
1-Mar	1-May	100	6800	6000
1-Apr	1-Jun	100	7200	6400

..remember to rearrange in your thinking, ignore column1, the base val

real		value	cumA	cumB
1-Nov		100	4400	3600
1-Dec		100	4800	4000
1-Jan		100	5200	4400
1-Feb		100	5600	4800
1-Mar		100	6000	5200
1-Apr		100	6400	5600
1-May		100	6800	6000
1-Jun		100	7200	6400

..we have the cumvalue to date and the cumvalue up to 8 weeks ago now in the same record...
and we easily measure the difference between cumulativevalueA and B and divide by 8
DEFINE FILE H1
MY8WKAVG/.. = (cumA - cumB) / 8 ;
END
..which in this example for Mar 1 is (6000-5200)/8 , or 100 average per week

then going forward, your deduction factor is now -100 for each of the next 12 weeks.
ONHAND = LAST ONHAND - MY8WKAVG;

Susannah,

MacGyver is Richard Dean Anderson also of Stargate SG1 fame. David Hasselhof was Knight Rider.

I watched the Mythbusters "MacGyver" episode last night and I didn't see a thing mentioning this technique. I guess I'll do some reading and hacking and I'll let you know what I come up with.

Thanks for the help so far...

re: hasselhof
Ginny, don't forget baywatch lol

quote:

Originally posted by GinnyJakes:
Frank is correct, MacGyver will work best as you have to generate 8 output lines for the single input row.

Since you already have the starting date and the average, you will calcuate the next date and inventory based on the value of the CNTR field in your FSEQ file. Of course, you need to read the doc on MacGyver first. Too much to put into the post.

JOIN BLANK WITH DATE TO BLANK IN FSEQ AS J1
DEFINE FILE filename
BLANK/A1 WITH DATE=' ';
DAYS_TO_ADD/I2=CNTR*7;
NEXT_WEEK/YYMD=DATE+DAYS_TO_ADD;
NEXT_INV/I6=IF CNTR EQ 1 THEN INV ELSE NEXT_INV - SALES_AVG;
END
TABLE FILE filename
PRINT NEXT_WEEK NEXT_INV
BY CNTR NOPRINT
WHERE CNTR LE 8
END  

You'll have to put your join in and create your FSEQ file first. Code not guaranteed but something to play with.

I like this idea but after reading all the McGuyver stuff I can't seem to get any of it to work. It's a little frustrating.

Anyways here is what I have:

I have a FOC Hold file HOLDTEST_SALESHISTORY with the following fields.

SDITM (Item No.)
TOT_LIPQOH (inventory)
LAST_8_WEEKS_AVG (weekly sales average)
DATE (YYMD)
DEPLETION (number of weeks to calculate)

This file will have one row of records.
Ex.

79638 | 1267060 | 111015 | 2008/02/26 | 12

From what I think i need to do is repeat this record 12 times (depletion) with a counter record added. Then I can do a compute on each where future date is ( DATE + COUNTER * 7 ) and future inventory will be ( TOT_LIPQOH - LAST_8_WEEKS_AVG * COUNTER ).

79638 | 1267060 | 111015 | 2008/02/26 | 12 | 1 | 2008/03/04 | 1267060
79638 | 1267060 | 111015 | 2008/02/26 | 12 | 2 | 2008/03/11 | 1156045
79638 | 1267060 | 111015 | 2008/02/26 | 12 | 3 | 2008/03/18 | 1045030
.
.
79638 | 1267060 | 111015 | 2008/02/26 | 12 | 12 | 2008/05/20 | 0

So with all that, I don't understand how to setup a FSEQ file. From what I've read it sounds like I add a blank field to my hold file (HOLDTEST_SALESHISTORY). Then I have a FSEQ file with a blank field and somehow either I join to it DEPLETION number of times or FSEQ has DEPLETION number of blank records to join to.

Here is what I have so far...fyi-of course it doesn't work.

JOIN BLANK WITH DATE TO BLANK IN FSEQ AS J1
DEFINE FILE HOLDTEST_SALESHISTORY
BLANK/A1 WITH DATE=' ';
DAYS_TO_ADD/I2=CNTR*7;
NEXT_WEEK/YYMD=DATE+DAYS_TO_ADD;
NEXT_INV/I6=IF CNTR EQ 1 THEN TOT_LIPQOH ELSE TOT_LIPQOH - LAST_8_WEEKS_AVG;
END

TABLE FILE HOLDTEST_SALESHISTORY
PRINT NEXT_WEEK NEXT_INV
BY CNTR NOPRINT
WHERE CNTR LE DEPLETION
END

I don't understand the FSEQ file setup though. I see some examples using DYNAM ALLOC and others don't. I'm lost here.

Speaking of Hasselhof, I would much rather be using the Baywatch method right now...

THAT'S IT!!! I'm creating a new coin phrase for IBI/Webfocus... "The Baywatch Method". That's where you post your problem on the forum and you sit back and watch while someone else solves it for you. What do you guys think?

Well actually I think I made some ground. I realized I had to create a FSEQ.DAT file and understand the blank + characters part. Is there a way to dynamically create the DAT file in the report so you can have a calculated number of lines?

Here is an example of creating an FSEQ file on the fly. The table request at the end is to show you that there is actually something in the table. You can put most of that code at the beginning of your program.

Also, if you type FSEQ in Advanced Search, you will get lots of hits on the various methods for creating it.

APP FI FSEQ DISK fseq.mas
-RUN
-WRITE FSEQ FILE=FSEQ,SUFFIX=FIX
-WRITE FSEQ SEGNAME=SEG1
-WRITE FSEQ FIELD=BLANK , , A1, A1, $
-WRITE FSEQ SEGNAME=SEG2, PARENT=SEG1, OCCURS=79
-WRITE FSEQ FIELD=WHATEVER, , A1, A1, $
-WRITE FSEQ FIELD=CNTR, ORDER, I4, I4,$
CHECK FILE FSEQ
APP FI FSEQDATA DISK fseq.ftm
-RUN
-WRITE FSEQDATA  12345678
APP FI FSEQ DISK fseq.ftm
TABLE FILE FSEQ
PRINT WHATEVER
BY CNTR
END