Printing Out Calculated Fields

[cawalton1]

I have several series of calculated fields which which relate to one another and I have an error in logic in one or more paths through the series. In the small font in the samll window that I have on the screen I am not able to find my error. Is there a way I can print out the expressions that make up the calculated fields? There are twenty of them.

Also, is there a rule that restricts the naming conventions of the calculated fields so that the field names must be alphabetically sorted, so that a field must already have been expressed (alphabetically named) before it can be used in a new calculated field?

[DaveM]

RE: Printing Out Calculated Fields

Clifford


I have several hundred calculated fields for different environments. I keep them in a word document in alpabetical order by description which is in bold. This seems to work for me.

I have calc fields in alpha that rely on calc fields and the alphabetical does not seem to matter

You might post those fields and let us look, we may figure it out. I have done this here before

Good Luck
Dave

[Stan Mathews]

RE: Printing Out Calculated Fields

In the "calculated fields" builder, left-click-hold on the small square box to the left of the first entry in the "name" column. Now drag down through your calculated fields (selecting them), release the mouse button, type ctrl-c to copy the fields, now you can paste the names and expressions into a word processor or spreadsheet, change the font, font size, and font color.

I like to paste into a word processor, change the font to Courier for monospacing, then play with the colors to differentiate various parts of an expression.

[cawalton1]

RE: Printing Out Calculated Fields

Thanks, that was a big help.

[cawalton1]

RE: Printing Out Calculated Fields

Using Stan Mathews suggestion I printed the fields in my word processor so I could what I was doing. I still had problems with "Years" not being found when I was trying to order my "Membership" group, however, I was having trouble sorting on my Noyears and Nomonths field in the "Membership" group, even after I eliminated all groups back to Detail just to see if I could order on these fields.

The Groups are:
Status
Membership
Group 3 (just used to draw a line every 7 lines)

After sorting or Status, then Membership, I wanted the Membership group to sort by Years and Months (using noyears and nomonths fields).

The following is the logic and fields used:


There are four dates involved in the record (including the system date):
A -- (earliest possible date - initial visit date [this date may be missing, when there is a membership date due to record keeping)"Visit_Date"
B -- (next date in possible order - membership date if joins)"Member_Date"
C -- (latest date entered - date leaving organization [wether or not becoming a member])"Change_Date"
D -- (system date, if no "Change_Date" (leaving) is applicable - still active in the organization at any point) "System->Date"

The calculations are used to determine:
1 - Time from visit / membership to active (person is active, whether or not becoming a member, visit date may be missing)
2 - Time from visit / membership to leaving (person left, whether or not becoming a member, visit date may be missing)
3 - Time interval (Yrs, Mos) earliest date (visit / membership) to latest date (active / leaving), (normal rounding criteria)
4 - Time interval set to 0, 0 when there are no visit / membership dates entered in the system yet.
5 - No time interval calculated if visit / membership dates are missing and leaving is present.

Earliest_Date if(isblank ("Member_Date"),Visit_Date,Member_Date)
Latest_Date if(isblank("Change_Date"),System->Date,Change_Date)
Months mod(round((jdate(Latest_Date)-jdate(Earliest_Date))/30,0),12)
Nomonths if(Months>12.or.Months40.or.years

[Stan Mathews]

RE: Printing Out Calculated Fields

If this is a calculated field

Years int((jdate(Latest_Date)-jdate(Earliest_Date))/365.25)

then you must refer to it with

CALC->Years if you want to use it as a grouping level. That may be why it could not be found.

As a side note, you might be advised to search the board for "Blank date". There has been much discussion of evaluating blank dates. "isblank ("Member_Date")"
(personally I never use the full word "date" in any field name, calculated field name, or variable name - just abbreviate as "dte" - color me cautious)

Also the use of "years" and "months" for your calculated field names, being similar names to Alpha functions, year() and month(), is not advisable. Might not cause a problem, but why take the risk.

[cawalton1]

RE: Printing Out Calculated Fields

I've put "Calc->" infront of all of the variables that needed it and the "years" warning stopped.

I made name changes to the years and months fields andnow have the following variables:

Earliest_Date if(isblank("Member_Date"),Visit_Date,Member_Date)
Latest_Date if(isblank("Change_Date"),System->Date,Change_Date)
Fmonths mod(round((jdate(Calc->Latest_Date)-jdate(Calc->Earliest_Date))/30,0),12)
Nomonths if((isblank("Visit_Date").and.isblank("Member_Date")),0,Calc->Fmonths)
Noyears if((isblank("Visit_Date").and.isblank("Member_Date")),0,Calc->Fyears)
Fyears int((jdate(Calc->Latest_Date)-jdate(Calc->Earliest_Date))/365.25)

I did not change the "Date" part of the variables.

Howver, even when I remove all of the groups and go for "Detail" section, I cannot get the dates to sort of Calc->Fyears+Calc->Fmonths. The sort works, but with in sub groups as if I still have groups sorting on other criteria. I'm still at a loss, but at leat soem of my code is now cleaned up a bit.
What next?

[Stan Mathews]

RE: Printing Out Calculated Fields

Are you comfortable with zipping up the table and it's associated files and attaching it here? Much easier to "hands on" an answer as opposed to merely speculating.

See here for what files to attach.

[cawalton1]

RE: Printing Out Calculated Fields

Attached are the files that give me the trouble of not being able to order at the group 2 level. The reprot to look at is HistoryDiff. Solving it here I can take it elsewhere if needed.

[cawalton1]

RE: Printing Out Calculated Fields

Thank you for considering the problem I presented with the data file I sent to you. I seem to have solved the problem by creating two new fields in the table (years, months) and using the "operation" update to apply the "expression" I had in my printout, but on which I could not sort.

Now I arrive at another problem to be solved in which I have been getting format and type errors in my equations, therefore, I must be going about the solution in a totally wrong fashion.

How do I solve the following problem?

I have records of individuals who are members of an organization, in particular, their First_Date attending and their Last_Date attending over the years.
I now want to determine how many were attending for each of a specified week during the year, where new members joined and others dropped out.
If the specified weeks were w1, w2, w3, etc., what kind of "operation" would I need to run to create a new file from which I can summarize the attendance statistics?
The thought process is:
w1=if(First_Date>="date1".and.Last_Date

[Tom Cone Jr]

RE: Printing Out Calculated Fields

Clifford,

You'll have better exposure for your second question if you don't bury it at the end of the first thread. It's also better 'form' since others with a similar problem will more easily find it if it is in its own thread.

In the meantime, perhaps you can furnish more details.

Assuming week1 begins January 1 (and of course it does not)...

Wouldn't week1 'participants' be folks whose first_date was equal to or earlier than January1, and whose last_date was equal to or greater than January 8?

Wouldn't week2 'participants' be folks whose first_date was equal to or earlier than January 8, and whose last_date was equal to or greater than January 15?

and so on, and so forth...

-- tom