9 Replies Latest reply on Mar 26, 2015 7:24 AM by Harry

    Chart X Axis Re Scales

    Harry

      Title

      Chart X Axis Re Scales

      Post

      Hi Board!


      I have a 'Months' table, Jan-Dec, 12 records.

       

      There are three fields in this table - Sales_Forecast, Sales_Entered, Sales_Shipped.

       

      Sales_Forecast is an entered number field.

      Sales_Entered is a Sum Calc from a table occurance

      Sales_Shipped is a Sum Calc from a table occurance.

       

      I've set up a column chart that displays the Months across the bottom, it works. The same chart has money on the Y and i've entered the scale and increments, so it holds and works OK.

       

      What I want to do is make one chart with Sales_Forecast, then another chart with Sales_Entered and put one on top of the other. That way, I get to control the colour of each chart. The 'Forecast' can be green, the 'Entered' can be Orange. One can fit underneath the other with transparency enabled.

       

      My problem is that if I make the first chart, copy it and then alter the Data for the Y axis to be the other Field, the Scale of the *X* axis changes.


      The boundary box of the charts in Layout mode are the same size, but when you click to Browse mode, the columns are spaced differently.

       

      Why is this?? I can't get it to line up correctly at all!

        • 1. Re: Chart X Axis Re Scales
          Harry

          Here's the two charts, Copy/Pasted on top of each other. I have mis-aligned them vertically so you can see the effect....

          • 2. Re: Chart X Axis Re Scales
            Harry

            And here's the same thing... all i've done is change the data on the 'Y' axis to be another field....

            • 3. Re: Chart X Axis Re Scales
              Harry

              The X axis has 'shrunk' itself. The boundary box is the same, the bars are not the same width. The space in between them is not even.

               

              If I stretch the boundary box, it can be made to fit over the top 'approximately' but it is a real bodge.


              I'm not sure what I'm doing wrong....

              • 4. Re: Chart X Axis Re Scales
                Harry

                I have used an 'IF' statement to set the Calc field to '0' if there is no SUM data, but that doesn't work.

                I've tried setting it to '50,000' if there's no SUM data, but that doesn't work, either.


                I thought perhaps that there was no data in the field, it would resize the scale.

                • 5. Re: Chart X Axis Re Scales
                  philmodjunk

                  Would a "stacked bar chart" work for you? the results would not be identical to what you are attempting here, but close and any scale changes would not result in a misalignment.

                  • 6. Re: Chart X Axis Re Scales
                    Harry

                    Hi PhilModJunk!

                     

                    Thanks for your post - 'Shaun' at FileMaker USA told me today that I should ask this here and wait for you to post; because you'd be able to solve it! So no pressure...

                     

                    A stacked chart doesn't really work for me in this instance, no. I primarily use this layering in dashboard data scenarios where targets are shown on a factory production floor. So there's the 'Target' underneath and then the actual live data 'grows' on top of it. It's also a really nice way to show the same sort of thing in sales data with transparency effects.

                    Any ideas? Is there something in the actual number data that defines the sizing of the columns and gaps...? There's like some chart-kerning going on and i can't see how to control it!

                     

                    Harry

                     

                    • 7. Re: Chart X Axis Re Scales
                      Harry

                      Nobody else have an insight into this?

                       

                      I will pay a FileMaker developer to provide information that fixes this!

                       

                      • 8. Re: Chart X Axis Re Scales
                        philmodjunk

                        Do both charts have all the same options specified except for the Y-series data?

                        I don't quite see why the second chart is a smaller size, but there are resize handles that should allow you to stretch the lower to match the upper, but I am doubtful that this will be a very effective design option for you as FileMaker doesn't give you a lot of direct control over the x axis scale.

                        On the other hand there alternative approaches to bar charts, from java scripted web viewers to the "old school" methods that we used to use before we had chart objects, such as a series of repeating fields that recreate the "bars" of the chart. This is a lot more design work to set up, but because each bar is a different layout object, you have much more control over how such multiple charts align with each other.

                        • 9. Re: Chart X Axis Re Scales
                          Harry

                          Hi!


                          Yes, all the other options are exactly the same. It's been copy/pasted and then the Y changed.

                           

                          Altering the size of the chart via the handles just stretches the chart. So the columns grow wider, the gaps are also wider. This means you can never really get them to line up properly. I have done this with one data set that has just 2 months data (this and last months) on the chart, on top. So they sort of line up but you can't see the other months 'going out' exponentially.

                           

                          I suppose I could have one chart per month, and then align those months etc. together.

                           

                          From what FM Support said, the answer was available somewhere!

                           

                          Thanks for your help with the suggestions.