Variable not availbale either in summary or running totals

[dadazs]

Hi,

The purpose of report is to generate audit results for clients for quarter.
Audits can be done monthly, bimonthly or quarterly.

I have manage to get those results by grouping clients, assigning variables for each month in quater and using max summary in the group.

Results are calculated fine; however, I am not able to calculate average results for specific manager that has x amount of sites because calculated field is not listed.

Formula below for ref:


Dim m1, m2, m3, last As Number
Dim audit As String
'last=tonumber(Maximum ({@previous month result}, {CONTACT1.COMPANY}))
'last={@last month reslut}
'm1={@current month result}
'm2={@next month result}
'm3={@current month result}

last=Maximum ({@last month reslut}, {CONTACT1.COMPANY})
m1=Maximum ({@current month result}, {CONTACT1.COMPANY})
m2=Maximum ({@next month result}, {CONTACT1.COMPANY})
m3=Maximum ({@last month reslut}, {CONTACT1.COMPANY})


audit={CONTACT2.UCSC}
if audit="Monthly" or audit=" Monthly" or audit=" Monthly" then
formula=(m1+m2+m3) / 3
end if 'monthly

if audit="Bi - Month" or audit="Bi-Monthly" then
if m1 > 0 and m2 > 0 and m3 > 0 then
formula= (m1+m2+m3) / 3
end if
if m1 > 0 and m2 >0 and m3 = 0 then
formula=(m1+m2) / 2
end if
if m1 > 0 and m2 =0 and m3 >0 then
formula=(m1+m3) / 2
end if

if m1 = 0 and m2 >0 and m3 >0 then
formula= (m2+m3) / 2
end if

if m1 >0 and m2 =0 and m3 =0 and last > 0 then 'valdiation of last month before quater begins
formula= m1
end if
if m1 >0 and m2 =0 and m3 =0 and last = 0 then 'valdiation of last month before quater begins
formula= m1 / 2
end if

if m1 =0 and m2 >0 and m3 =0 and last > 0 then 'valdiation of last month before quater begins
formula= m2
end if
if m1 =0 and m2 >0 and m3 =0 and last = 0 then 'valdiation of last month before quater begins
formula= m2 / 2
end if

if m1 =0 and m2 =0 and m3 >0 and last > 0 then 'valdiation of last month before quater begins
formula= m3
end if
if m1 =0 and m2 =0 and m3 >0 and last = 0 then 'valdiation of last month before quater begins
formula= m3 / 2
end if



end if 'bi monthly


if audit="Quarterly" or audit="TRI-Annual" then
if m1 > 0 and m2 > 0 and m3 > 0 then
formula= (m1+m2+m3) / 3
end if

if m1 > 0 and m2 >0 and m3 = 0 then
formula=(m1+m2) / 2
end if

if m1 > 0 and m2 =0 and m3 >0 then
formula=(m1+m3) / 2
end if

if m1 = 0 and m2 >0 and m3 >0 then
formula= (m2+m3) / 2
end if

if m1 = 0 and m2 = 0 and m3 >0 then
formula= m3
end if
if m1 = 0 and m2 > 0 and m3 =0 then
formula= m2
end if

if m1 > 0 and m2 = 0 and m3 =0 then
formula= m1

end if

end if

 

[dadazs]

http://www.youtube.com/watch?v=atflNygyXuQ

link above shows where variable is missing

 

[lbass]

The problem is your use of a maximum in the variable. This approach appears to be more complicated than necessary. Your sample data is confusing though, as you appear to be setting last and m3 equal to the same value. I also am not seeing how the interval for the audit is affecting the formulas. Can you explain more? There is probably a simpler approach that would also allow you to insert summaries.

-LB

 

[dadazs]

Thanks LB,
well spoted - it had to be:
last=Maximum Maximum ({@previous month result}, {CONTACT1.COMPANY})

I will try to explain the report structure:
Site Frequency Quater Result M1 Result M2 Result M3 Result
_--General Manager
_----Supervisor
_------Site
Site1 monthly 12345 46846 468464 484684
Site2 bimonthly 54321 16874 46848
Site3 querterly 19846 486464

Values for months 1 to 3 (M1-M3) come from the same-single field in the database. Using formulas and aggregate functions let me break it down into months columns and display data horizontally instead of vertically.
It is the main reason why I use aggregate function so fields appear in single line in the group header.
Interval directs how to calculate average for site.

 

[lbass]

Now you are showing "Maximum Maximum"...

Please show the content of each of the nested formulas so I can understand the criteria.

-LB