Averages Work manually but not in procedure

An .intialize procedure I have been using daily for probably 15 years (in a DB that is approaching 30) stopped working correctly this afternoon. It was after a series of PanX crashes/force quits, so something may have gotten corrupted. If so, I don’t know how far the corruption has gone, this is just the most obvious problem to appear.

There are other calculations going on before this, but all records are selected and summaries clear before the following.

I select five years worth of data and then average then numbers as follows. All fields are integers

field «Total»
field «Tix1»
field «Tix2»
field «Tix3»
field «Tix4»
Field «Dist. Gross»

fg5Total="Five Year Avg Total: "+«Total»
fg5Gross="Five Year Avg Gross: "+pattern(«Dist. Gross»,"#,#.##")
fg5Mem="Five Year Avg Member: "+«Tix1»
fg5Non="Five Year Avg NonMem: "+«Tix2»
fg5Coup="Five Year Avg Coupon: "+«Tix4»
fg5Child="Five Year Avg Child: "+«Tix3»

When I look at the data sheet at this point «Tix1» summary filed is blank. But if I manually average the field, the correct number is calculated and placed in the field.

If I stop the procedure here and then run it again, «Tix1» and «Tix2» summary fields are blank.

I just ran it a third time, and all fields are calculated and correct. But the fourth time, back to blank

Any reason the behavior would change if not corruption?

I think you could distill all your code down to only these 6 lines by using the aggregate( function thus eliminating the need for summary records.

fg5Total="Five Year Avg Total: "+aggregate({«Total»},"average")
fg5Gross="Five Year Avg Gross: "+pattern(aggregate({«Dist. Gross»},"average"),"#,#.##")
fg5Mem="Five Year Avg Member: "+aggregate({«Tix1»},"average")
fg5Non="Five Year Avg NonMem: "+aggregate({«Tix2»},"average")
fg5Coup="Five Year Avg Coupon: "+aggregate({«Tix4»},"average")
fg5Child="Five Year Avg Child: "+aggregate({«Tix3»},"average")

There have been a couple of reports of problems in calculating summary values here on the forum. I have not been able to duplicate these problems, and unfortunately have no suggestions at this time as to what might be going on.

However, if the ultimate point of this code is to get averages into a series of fileglobal variables, I would suggest changing to use the aggregate( function. You could replace your entire procedure with this

letfileglobal fg5Total = "Five Year Avg Total: "+aggregate({Total},"average")
letfileglobal fg5Gross = "Five Year Avg Total: "+pattern(aggregate({«Dist. Gross»},"average"),"#,.##")
letfileglobal fg5Mem = "Five Year Avg Member: "+aggregate({Tix1},"average")
letfileglobal fg5Non = "Five Year Avg NonMem: "+aggregate({Tix2},"average")
letfileglobal fg5Coup = "Five Year Avg Coupon: "+aggregate({Tix3},"average")
letfileglobal fg5Child = "Five Year Avg Child: "+aggregate({Tix4},"average")

If done this way, you wouldn’t have to worry about getting rid of the summary record later.

Yeah, when I looked at it I thought I could change it to aggregate(, but you know, if it ain’t broke etc. But now that it’s broke and I have to work on it, may as well convert it to aggregate and see what happens.

Using aggregate( solved the problem, but I did have to use pattern( on each to get the integer display I wanted. Using the original code, pattern( was only necessary to display a dollar amount.

I don’t know if either of those caused the error deep in the bowels of the source code.


Correct, I expected that. I thought it was a feature – guess not in this case. The average statement puts the averages into the field itself, so of course for an integer field you get integers. Many users are displeased at this since the average might not be an integer. The aggregate( function always calculates using floating point. Instead of using the pattern( function, you could also have used the int( function. Either is fine.

This sounds very similar to my problem doing Totals on Grouped Data mentioned previously in the forum. I imagine there would be a way to use aggregate to help me through until whatever the problem is is found and fixed. I’ve imagined it would require some sort of array to get the names of the groups, and then generate them into a loop to calculate the aggregate totals for each group and put the totals into the right place?? However, that is beyond my programming skills. If anyone is able to help out with a generic formula for this process that I (and everyone else with the same problem) can adapt, I’d be very grateful. At the moment I’m stopping the procedure at the point where I want the totals to be calculated, manually doing the totals, then continuing with the procedure.

Martin is not using grouped data, so the aggregate( function is really a superior solution. For subtotals though, aggregate is not appropriate and would be very complicated. Depending on your application, the summarytable( function might possibly be helpful.