Hi,
The purpose of report is to generate audit results for clients for quarter.
Audits can be done monthly, bimonthly or quarterly.
I have managed 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.
I need to know what is an average of audit scores for manager; however,
calculated field(formula below) is not available either in summary or running totals. Please help.
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
IP Logged
