Oscillating Procedure

Panorama X Beta 10.2
1

I wrote a procedure which is designed to display three values: a total 12 mo average, the 12 mo average for groups with the percentage of the total of those groups; and the 12 mo average for individuals with the percentage of the total for those individuals.
The following procedure returns the correct value for the total 12 mo average every time. One time it is run, the 12 mo average for groups is correct, but the 12 mo average for individuals is incorrect. The next time the procedure is run, the 12 mo average for the groups is incorrect, but the 12 mo average for individuals is correct. It oscillates back and forth. What is causing that? I can not figure it out.

local lv12mosum, lvgroups, lvindividuals
opensheet
selectall
field «12mosum»
total
lastrecord
copycell
lv12mosum=val(clipboard())/12
removesummaries 7
selectall
field «type»
select «type»=“group”
field «12mosum»
total
lastrecord
copycell
lvgroups=val(clipboard())/12
removesummaries 7
selectall
field «type»
select «type»=“individual”
field «12mosum»
total
lastrecord
copycell
lvindividuals=val(clipboard())/12
removesummaries 7
selectall
message “The 12moave is: “+pattern(lv12mosum,”$#,.##”)+cr()+cr()+“From groups: “+pattern(lvgroups,”$#,.##”)+" ("+pattern((lvgroups100)/lv12mosum,"##.#")+"%)"+cr()+cr()+“From individuals is: “+pattern(lvindividuals,”$#,.##”)+" ("+pattern((lvindividuals100)/lv12mosum,"##.#")+"%)"
closewindow
beep

2

My guess is that the clipboard is the cause of your problem so I would replace the three instances of code:

copycell
lv12mosum=val(clipboard())/12

with:

lv12mosum=«12mosum»/12

and…

copycell
lvgroups=val(clipboard())/12

with:

lvgroups=«12mosum»/12

and finally…

copycell
lvindividuals=val(clipboard())/12

with:

lvindividuals=«12mosum»/12

You are already putting the focus on the cell you want so there is no need to copy it to the clipboard for processing - just use it directly in the code.

3

I think you can do this much more efficiently by using aggregate statements. You do not need to open the datasheet or make any selections or calculate any totals. Like this:

selectall
let lv12mosum=aggregate({«12mosum»},"sum")/12
let lvgroups=aggregate({«12mosum»},"sum",{type="group"})/12
let lvindividuals=aggregate({«12mosum»},"sum",{type="individual"})/12
message ....
1 Like
4

This was really helpful. I’m still learning how to use some of the newer functions in PanX. This was much faster and did the job every time. Thanks again.