Complaint Index on Horizontal Bar Graph
NaturSalus Jan 2, 2014 8:15 AMTitle
Complaint Index on Horizontal Bar Graph
Post
Hello,
I am trying to represent the “Complaint Index” using a horizontal bar graph but I am not getting the right numbers, so my rationale for carrying out the calculation must be wrong and I am asking help to find out where is my mistake.
The Complaint Index = (Total complaints per year / Total sales in units per year) x Sales factor
Tables:

Complaint

Sales
Complaint table fields:

Complaint::cComplaintYear [Calculation = Year (ComplaintDate)]. Calculation result is: Number]

Complaint::TotalCountYear [Summary = Count of cComplaintYear]
Sales table fields:

Sales::UnitsSold (Number)

Sales::Year (Number)

Sales::SalesFactor (Number)
TOs:

ComplaintTO based on the Complaint table

SalesTO based on the Sales table

ComplaintSalesTO based on the Sales table
Relationships
ComplaintTO::cComplaintYear = ComplaintSalesTO::Year
Horizontal Bar Graph
The graph is on the Complaint Index Report layout and has the following settings:
XAxis
Title: “Complaint Index”
Data: (ComplaintTO::TotalCountYear / ComplaintSalesTO::UnitsSold) * (ComplaintSalesTO::SalesFactor)
YAxis
Title: “Year”
Data: ComplaintTO::cComplaintYear
The graph is generated by the Complaint Index script that sorts data based on the Complaint::cComplaintYear field.
Let's imagine that:
 during 2012 a total of 16 complaints where filled and 5000 units where sold and the Sales Factor used to calculate the Complaint Factor is 1000
 during 2013 a total of 6 complaints where filled and 3000 units where sold and the Sales Factor used to calculate the Complaint Factor is 1000
Based on these data:

for year 2012, the Complaint Index = 1000x (16/5000) = 3,2

for year 2013, the Complaint Index = 1000x (6/3000) = 2,0
however what I am getting with my setting is the following:

For year 2012, the Complaint Index = 2,98

For year 2013, the Complaint Index = 7,95
Since I can represent without error the total number of complaints per year using a horizontal bar graph, so the problem must be related to how I calculate the Complaint Inex under FileMaker.
Thanks