Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Complicated report that I need to create
Go
New
Search
Notify
Tools
Reply
  
Complicated report that I need to create
 Login/Join
 
<levada>
posted
Please email responses to elliot.siegel@juliusbaer.com as I dont have much internet access.
Here is my sample data
Date Beginning Bal Inflow Outflow End Bal
20040131 100 10 5 105
20040228 105 30 40 95
20040331 95 100 0 195
20040430 195 10 50 155


Here is the report that I want to create
Beginning Bal Total Inflow Total Outflow End Bal
100 150 95 155

Which means that my basic criteria are that I take the begining balance from the earliest month and the end bal from the latest month and show the sum of the total inflow and the total outflow.

If you could let me know how I can do this I would greatly appreciate it. I tried using a compute field to compare the value of the date to the min and the max and if the value of the date is the min then use the value for the begining bal otherwise use 0 and with similar logic for the end bal. However, it seems like I cant use a calculated field on a row by row basis. here is the fex that i used. note that I did not included the endbal as it uses the same type of logic as begbal.

TABLE FILE WF_NNM_MIS_REGION
SUM
COMPUTE BEGBAL = IF ASOFDATE NE '&ASOFMIN' THEN 0 ELSE OPENING_BALANCE; AS 'BEG'

NNM_INFLOW
NNM_OUTFLOW
 
Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Your making harder than it is.
Try the following code that sums the inflow and outflow but gives the first beginning balance and the last ending balance. This would output the numbers in your example.

table file filename
sum fst.beginningbal inflow outflow lst.endbal
end
 
Posts: 115 | Location: Chicago, IL | Registered: May 28, 2004Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Complicated report that I need to create

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.