October 20, 2004, 01:38 PM<levada>
Complicated report that I need to create
Please email responses to firstname.lastname@example.org 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
COMPUTE BEGBAL = IF ASOFDATE NE '&ASOFMIN' THEN 0 ELSE OPENING_BALANCE; AS 'BEG'
October 20, 2004, 02:00 PMET
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