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     Create a running total by weeks

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Create a running total by weeks
 Login/Join
 
Gold member
posted
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.


------------------------------------------
DevStudio 8.2.03
WFS 8.2.03
 
Posts: 86 | Location: Atlanta | Registered: May 10, 2007Report This Post
Expert
posted Hide Post
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.


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
Virtuoso
posted Hide Post
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

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Expert
posted Hide Post
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.


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
Expert
posted Hide Post
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;




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Expert
posted Hide Post
Susannah,

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


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
Gold member
posted Hide Post
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...


------------------------------------------
DevStudio 8.2.03
WFS 8.2.03
 
Posts: 86 | Location: Atlanta | Registered: May 10, 2007Report This Post
Platinum Member
posted Hide Post
re: hasselhof
Ginny, don't forget baywatch lol


aix-533,websphere 5.1.1,apache-2.0,
wf 538(d), 537 (p),
==============
7.6.11 (t) aix 5312
websphere 6.1.19
apache 2.0
 
Posts: 195 | Registered: October 27, 2006Report This Post
Gold member
posted Hide Post
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.


------------------------------------------
DevStudio 8.2.03
WFS 8.2.03
 
Posts: 86 | Location: Atlanta | Registered: May 10, 2007Report This Post
Gold member
posted Hide Post
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?


------------------------------------------
DevStudio 8.2.03
WFS 8.2.03
 
Posts: 86 | Location: Atlanta | Registered: May 10, 2007Report This Post
Gold member
posted Hide Post
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?


------------------------------------------
DevStudio 8.2.03
WFS 8.2.03
 
Posts: 86 | Location: Atlanta | Registered: May 10, 2007Report This Post
Expert
posted Hide Post
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
  


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
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Create a running total by weeks

Copyright © 1996-2020 Information Builders