AnsweredAssumed Answered

Complaint Index on Horizontal Bar Graph

Question asked by NaturSalus on Jan 2, 2014
Latest reply on Jan 3, 2014 by NaturSalus

Title

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:

X-Axis

Title: “Complaint Index”

Data: (ComplaintTO::TotalCountYear / ComplaintSalesTO::UnitsSold) * (ComplaintSalesTO::SalesFactor)

Y-Axis

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

Outcomes