10 Replies Latest reply on Jan 3, 2014 9:05 AM by NaturSalus

    Complaint Index on Horizontal Bar Graph

    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

        • 1. Re: Complaint Index on Horizontal Bar Graph
          philmodjunk

               First thing that I'd check is to see if the three values used in the calculation are actually returning the values you expect to be used for this calculation. Using the year 2012 example, I'd check to see if I am really getting values of 1000,   16,     and 5000.

          • 2. Re: Complaint Index on Horizontal Bar Graph
            NaturSalus

                 Hello Phil,

                 Thanks for looking into my question.

                 The values 1000 and 5000 are entered by the user into a Sales record for the year 2012.

                 The value 16 is displayed correctly by the horizontal bar graph because I get that value when the X axis gets its value from the field: ComplaintTO::TotalCountYear.

                 I tried what you suggested using the Script Debugger while running the Complaint Index script but I couldn't see what values where used and returned  by the calculation of the X axis.

                 I guess there must be a way, that I don't know,  to see in action the calculations for the X and Y axis.

                 Maybe the problem lies in the Complaint Index script:

                 Enter Browse Mode [ ]

                 Go to Layout ["Complaint Index Report" (Complaint)]

                 Show All records

                 Sort Records [Restore; No dialog]. Complaint::cComplaintYear

                 Enter Preview Mode [ ]

                 Go to Layout [original layout]

                 Enter Browse Mode [ ]

                 Unsort Records

            • 3. Re: Complaint Index on Horizontal Bar Graph
              philmodjunk

                   Since you are charting the results of a simple math expression, at least one of those three values must not be correct in the context where it evaluates.

              I couldn't see what values where used and returned  by the calculation of the X axis.

                   Copy the expression into the data viewer as a watch expression, then run the script with the debugger enabled. You can check the value of a watch expression at any point in your script.

                   But what is the script doing? I don't how an x-Axis calculation (shouldn't that by the Y-series data?) would need to evaluate within a script--that doesn't make any sense.

              • 4. Re: Complaint Index on Horizontal Bar Graph
                NaturSalus

                 I don't how an x-Axis calculation (shouldn't that by the Y-series data?)

                Sorry, I am not sure I get you.

                      

                     In order to get on the X axis the "Complaint Index" I though that all I had to do was to use the following calculation:

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

                      

                The Y-series simply provides the year and gets it right (as expected) using the following calculation: Complaint::cComplaintYear

                     The X- series provides the Complaint Index and gets it wrong (not as expected) using the  following calculation: (ComplaintTO::TotalCountYear / ComplaintSalesTO::UnitsSold) * (ComplaintSalesTO::SalesFactor)

                     The Complaint Index calculation has three components:

                       
                •           ComplaintTO::TotalCountYear 
                •      
                •           ComplaintSalesTO::UnitsSold
                •      
                •           ComplaintSalesTO::SalesFactor

                      

                TROUBLESHOOTING

                     In order to troubleshoot I first checked that the ComplaintTO::TotalCountYear field was giving the expected value. As said before, I did it setting up a horzontal X-bar that gives:

                       
                •           on the Y axis the year. Calculation = Complaint::cComplaintYear
                •      
                •           on the X axis the total of complaints for each year. Calculation = ComplaintTO::TotalCountYear

                      

                     Since I got the expected values I decided that both calculations were working as expected.

                      

                     So what was left was to ckeck out is whether the calculation for the X-axis gets the right values per year for the following 2 fields:

                       
                •           ComplaintSalesTO::UnitsSold
                •      
                •           ComplaintSalesTO::SalesFactor

                      

                     I though that running the Complaint Index script using the Script Debbuger I would be able to see what was getting in and out of the calculation for the X axis, but I was wrong.

                     I tried your suggestion but all I got was the sum of the wrong values of the Complaint Index for thje years 2012 and 2013.

                      

                       
                •           

                  For year 2012, the Complaint Index = 2,98

                       
                •      
                •           

                  For year 2013, the Complaint Index = 7,95

                       

                     After copying the expression ((ComplaintTO::TotalCountYear / ComplaintSalesTO::UnitsSold) * (ComplaintSalesTO::SalesFactor)) into the data viewer as a watch expression

                     I am getting as a result: 2,98 + 7,95 = 10,93

                     This total doesn't help me.

                      

                      

                     In orther to troubleshoot it I need to know whether the X- axis calculation  ((ComplaintTO::TotalCountYear / ComplaintSalesTO::UnitsSold) * (ComplaintSalesTO::SalesFactor)) gets right

                       
                •           ComplaintSalesTO::UnitsSold for the year 2012
                •      
                •           ComplaintSalesTO::SalesFactor for the year 2012

                      

                     and if it calculates correctly the Complaint Index

                      

                     Since I can't check what gets into the X-axis calculation and waht gets out of it, the only thing I can think of is whether the established relationship between ComplaintTO and ComplaintSalesTO is the correct one for my purposes.

                      

                     Do you consider the relationship: 

                     ComplaintTO::cComplaintYear = ComplaintSalesTO::Year

                     the correct one to make sure that the X axis gives the Complaint Index per year?

                      

                 I don't how an x-Axis calculation  would need to evaluate within a script--that doesn't make any sense.

                     Sorry if I expressed myself poorly, but in order to have the horizontal X bar working I need first to sort by year and then get into preview mode, is there another way?

                • 5. Re: Complaint Index on Horizontal Bar Graph
                  philmodjunk

                       Normally the X-axis is for independent data--the year and Y-Axis data is for the dependent data--data that varies with each year. Even in a horizontal bar chart, the Y-axis data series are used for the dependent data--you'll find that it is specified for the horizontal axis instead of the vertical in this special case.

                       Please explain this script. I don't understand what role it plays in producing this graph.

                  • 6. Re: Complaint Index on Horizontal Bar Graph
                    NaturSalus

                    Normally the X-axis is for independent data--the year and Y-Axis data is for the dependent data--data that varies with each year. Even in a horizontal bar chart, the Y-axis data series are used for the dependent data--you'll find that it is specified for the horizontal axis instead of the vertical in this special case.

                    I will not argue on this point. But even after the shift I get the same unexpected values, as expected.

                          

                    Please explain this script. I don't understand what role it plays in producing this graph.

                    My first intend was to be able to graph the Complaint Index.

                    Anyhow, it is true that I don't need any script to see the graph results as long as I sort out the values.

                    In my set up, the user can graph the Complaint Index and later print out simply by clicking on a button that runs the Complaint Index script.

                    As a matter of fact this has been my current setting for showing other graphs.

                          

                    From your questions am I right assuming that you think that the established relationship is correct?

                    • 7. Re: Complaint Index on Horizontal Bar Graph
                      NaturSalus

                           Okay some findings that could help you to help me.

                           Based on the weird results and some basic mathematics I have figured out what is doing FileMaker with the current setting,

                           My purpose was to assure that FM was carrying out the following calculation:

                           Complaint Index = (Total complaints per year / Total sales in units per year) x Sales factor

                           However, what Fm is doing with my current setting is the following:

                           The Complaint Index = (Total complaints per year / Year) x Sales factor

                           so, the field ComplaintSalesTO::UnitsSold instead of proving the units sold that year what is providing is the year.

                           Based on this and your vast knowledge on db could you guide me to set up the correct relationship between

                           the Complaint table

                           and 

                           the Sales table?

                            

                           Thanks

                      • 8. Re: Complaint Index on Horizontal Bar Graph
                        philmodjunk

                             It seems correct, but the context and settings for your chart may be at issue. On what table occurrence is your chart's layout based? What data source options have you selected for that chart?

                             Since a chart displays data from the records in your database, can you set up a "data table" view of the data being charted where you see both the computed index and the three values used to compute it? That might provide a clue as to why this isn't working.

                        • 9. Re: Complaint Index on Horizontal Bar Graph
                          NaturSalus

                          On what table occurrence is your chart's layout based?

                               is based on the Complaint TO

                          What data source options have you selected for that chart?

                               Current found set

                          Summarized group of records

                                

                          • 10. Re: Complaint Index on Horizontal Bar Graph
                            NaturSalus

                            Since a chart displays data from the records in your database, can you set up a "data table" view of the data being charted where you see both the computed index and the three values used to compute it? That might provide a clue as to why this isn't working.

                            Okay figured out!

                            My mistake, I was using the wrong field in the calculation on the Complaint Index.

                            As a matter of fact I was forcing FM to divide by the year and not by the total units sold.

                            Thanks