1 2 Previous Next 17 Replies Latest reply on Feb 5, 2013 1:02 PM by philmodjunk

    Stacked Bar Chart - FMP12

    KennySolway

      Title

      Stacked Bar Chart - FMP12

      Post

           Hello,

           I would appreciate assistance in creating a Stacked Bar Chart report for my sales data.  

           The data is pretty standard.  I have one field that is called 'Delivery Month' which indicates the date in which the engagement is to be delivered.  I have a second field called 'Sales Stage' which indicates how far along the sales funnel the engagement is (i.e. Qualified, Proposal Submitted, Verbal Confirmation, Won, etc.).  I have a third field named 'Price' which indicates the expected revenue from that engagement.

           With these three fields, I would like to create a stacked bar chart with the x-axis reflecting the months of the year and the y-axis reflecting the stage of sale and total revene for each engagement within each month.  So one bar for any given month would reflect the dollar value total for each of the sales stages.

           If more details are required, please advise.  I thank you in advance for any assistance you're able to provide.

        • 1. Re: Stacked Bar Chart - FMP12
          philmodjunk

               More detail is required, I think.

               A stacked bar needs multiple number values, each in a different "series" for the Y-Axis. I can't tell from your description what values, other than Price would work for that bar.

               Does each bar represent a group of records in your table?

          • 2. Re: Stacked Bar Chart - FMP12
            KennySolway

                 Ideally, each bar represents two things.  First, the amount of revenue expected on that particular months.  Second, the amount of expected revenue in each stage for that month.  The x-axis would be each month of the year - so twelve bars across.

                 Fields to use are 'Delivery Month', 'Sales Stage', 'Price'.  I'm not sure else how to describe it.  I could draw out a diagram and send that to you.  Would that help?

            • 3. Re: Stacked Bar Chart - FMP12
              KennySolway

                   Here is exactly what I'm looking to create.  This was created in salesforce using the exact same data.  Please see attached.

              • 4. Re: Stacked Bar Chart - FMP12
                philmodjunk

                     Does each bar represent a group of records in your table?

                     I believe that it does from what I see here, but would like confirmation.

                • 5. Re: Stacked Bar Chart - FMP12
                  KennySolway

                       I don't undrestand the question.  What do you mean by 'group of records'?  When you say 'each bar', I interpret that to mean each month as the bar visually represents the data in any given month.  

                  • 6. Re: Stacked Bar Chart - FMP12
                    philmodjunk

                         You have one bar for each month in your example.

                         But when you examine the data you need to use to produce that single stacked bar are you pulling data from a single record or a group of records--all with dates falling in that given month?

                    • 7. Re: Stacked Bar Chart - FMP12
                      KennySolway

                           yes...one bar for each month...

                           here is a screen shot of my data.  I believe i'm pulling data from a group of records - but I don't really understand the difference between records or data

                      • 8. Re: Stacked Bar Chart - FMP12
                        philmodjunk

                             Presumably, each row in your last screen shot is a different record. I thus see 6 records (rows) of sales data for the month of February that provide the numbers needed to produce the bar for that month and year.

                             The answer to my question makes a critical difference in how you set up your bar chart as you can set it up to acquire the needed data series's for the chart in a number of different ways. I didn't want to suggest a method that didn't work for the way your data was structured in the table.

                             First thing that I will suggest is that you set up cMonth as a calcualtion field with "date" selected as the return type:

                             Delivery Date - Day ( Delivery Date ) + 1

                             This will return a date for the first day of the month that contains the Delivery Date. This makes for a very useful field for sorting your data into groups by month. Not only will it properly group all records with dates from the same month, but they also will sort into correct chronological order.

                             This one gets a bit "messy" with FileMaker's method for producing the individual parts of the stacked bar.

                             You'll need to add two fields for every sales stage that you have. For "verbal Confirmation", for example, you'd define cVerbalConf and sVerbalConf as follows:

                             cVerbalConf: If ( Sales Stage = "Verbal Confirmation" ; Price )

                             And then you define sVerbalConf as a summary field that computes the total of cVerbalConf

                             You then specify cMonth as your X axis data field using this calculation: MonthName ( cMonth )

                             And then you defy a Y data series for each summary field--one for each sales stage.

                             On the Data source section, you then specify that the data source is for the found set, groups of records when sorted.

                             The final requirement is to perform a find for all the records you want to use to produce your chart and then you must sort your recorsd by cMonth to get the needed bars on your chart.

                        • 9. Re: Stacked Bar Chart - FMP12
                          KennySolway

                               ok...kind of get it, but there seems to be something missing on my end as i can't follow your steps...although I'm trying to do exactly as you say and type...

                               i have cMonth working in the table and created the two fields for every stage - but no calculations appear and when i go to set up table, data does not appear.

                               is there a faster way for you and I to communicate i.e. via chat or something as I"m concerned that there are so many minute details that if one gets lost, we're both wasting a lot of time...any suggestions?

                          • 10. Re: Stacked Bar Chart - FMP12
                            KennySolway

                                 can i not just send you the file to make the changes?  that would seem to take the least amount of time and effort on your part.

                            • 11. Re: Stacked Bar Chart - FMP12
                              philmodjunk

                                   Make sure that the calcualtion fields are of type calculation, not number fields with an auto-entered calculation.

                              • 12. Re: Stacked Bar Chart - FMP12
                                KennySolway

                                     they y axis seems to be working in that i now have stacked bars, however the x axis calculation does not work

                                     when i enter as you suggested

                                     "You then specify cMonth as your X axis data field using this calculation: MonthName ( cMonth )"

                                I get an error message indicating 'The specified table cannot be found'.

                                     Please advise as it appears we're getting close.  Thanks for your guidance so far.

                                • 13. Re: Stacked Bar Chart - FMP12
                                  philmodjunk

                                       Add cmonth to the calcuation by double clicking the field name from the list of fields in upper left portion of the dialog.

                                       This will add both the table occurrence name and the field name to produce something like this:

                                       MonthName ( Yourtable::cMonth )

                                  • 14. Re: Stacked Bar Chart - FMP12
                                    KennySolway

                                         i unfortunately don't understand and I'm just guessing what you're trying to tell me to do...see attached for what I've got so far. it appears that not only are the months not appearing as expected along x axis but htat the values of the calculations aren't accurate either.  please advise.

                                    1 2 Previous Next