1 2 Previous Next 19 Replies Latest reply on Jan 15, 2014 11:15 AM by philmodjunk

    Charting Expiration Dates

    TylerNelson

      Title

      Charting Expiration Dates

      Post

           Hi!

           So my client wants a chart that he thinks is simple, but I'm racking my brain to build it in Filemaker. 

           I have a Projects (Films/TV shows) table and each Project some of the projects have "Options" with 2 expirations dates, one for the initial term and another for the extended term (generally 2-5 years). 

           The Client wants a stacked bar chart with the list of optioned Projects on the Vertical Axis (Easy) and a timeline on the horizontal axis extending over the course of say 4-5 years with bars that represent the lifetime of each project ending on the expiration date. 

           Can anyone suggest a calculation for the X-Asis data using the 2 expiration dates to plot the bars on the horizontal axis???

           I have a calculation field in Projects that calculates the number of days left in each option term. But if I chart that it won't be exactly linear... I know my client envisions the horizontal axis to include every month, but I don't know if that's possible.

           I'm basing the Graph in a globals table with 1 record and relating it to Projects (delimited data) with a Constant1 = BooleanField for Projects that have Options. Maybe this will require putting the graph in a different context...

           Thanks!

           Stephen

        • 1. Re: Charting Expiration Dates
          philmodjunk

               To get a scale of one point for every month, you'll need either a set of records with at least one record for every month or a list of delimited values with one value for every month. Thus may require a special "charting" table where you can create such records and link to your projects table.

          • 2. Re: Charting Expiration Dates
            TylerNelson

                 Thanks PhilModJunk! I've set up charts in the past through a "charting" table using aggregate math to get the total of transactions per month. So this concept is familiar to me. 

                 I created a demo file that tries to illustrate exactly what I'm trying to do in a simplified framework. I hope this Dropbox link works:

            https://www.dropbox.com/sh/ixmf6kt7j5rmytk/uRQaIrtw7S

                 The method I'm trying to use for the chart I set up is to base the chart in a Globals table, which is related to a Months table with only 24 records (related via a Cartesian join from a Constant1 field to the MonthNumber, sorting the MonthNumber). 

                 However, I'm still racking my brain to figure out how this will work with expiration dates. Especially since some of the expiration dates fall outside of the 24 month period, so theoretically the bar should simply go to the end of the chart. From the context of this new months table I can get a list of Projects whose Options expire during any given month along with the expiration date. But this still doesn't allow me to plot specific values on the chart from the context of the Globals table.

                 Ideally the resulting chart should plot the expiration dates if they fall somewhere on the chart. Or at least it should show a bar for each project ending at the month containing the expiration date. I can easily calculate which options are active during a given month, which I did in the sample file. But I'm still not sure how to plot that on a bar chart. Any suggestions?

                  

            • 3. Re: Charting Expiration Dates
              philmodjunk

                   Your link doesn't work when I click it. But I was able to copy/paste the text into a URL box in my browser to get to and download the file.

                   But I've been having a lot of trouble visualizing how you want this to work. A quick look at your chart example helped.

                   So each bar is a single project?

                   Each bar will have two parts stacked together with the first part representing the initial term and the second part representing the extended term?

                   And each Y axis "tick" is one month?

                   It sounds like we could chart the difference in months between today (or a specified date) and the initial and extended expiration dates as two different "y-axis data series" to get the chart that you want.

              • 4. Re: Charting Expiration Dates
                TylerNelson

                     Yeah, what you're describing with the bars makes sense. I imagined it as a stacked bar chart, but I guess it could be different. I just have to make sure my client likes it. One of these options goes out to 2016 and another is until 2019, so I think those can simply run off of the chart, since their expiration is not impending. Thanks!

                      

                • 5. Re: Charting Expiration Dates
                  TylerNelson

                       Here's a little mockup I made in Powerpoint, not using any real charting tools

                  • 6. Re: Charting Expiration Dates
                    philmodjunk

                         I think that a simple stacked bar chart can do this. Next question is whether 30 day "months" are sufficient or if we have to gin up a more complex calculation that computes actual calendar months.

                         And I'm not sure about the "run off the chart" bars. It may or may not take a bit of trickery to handle that. I forget the actual scale options now possible in FileMaker 12 or 13 so we'll have to take a look and see.

                    • 7. Re: Charting Expiration Dates
                      TylerNelson

                           Oh yeah! I've always wondered how to use Logorithmic scaling for charts. Maybe this is the right instance to employ it, so that the bars don't run off of the chart. Although I imagine that the Months will become unreadable at the bottom, unless I stretch it out like crazy, if I try to incorporate options that last until 2019, for example. 

                           I don't think I need actual calendar months. I can't imagine that the chart could actually be configured to display the expiration dates, especially if we are plotting the total time between CurrentDate and ExpirationDate.

                           I do think that the months table should be set up dynamically to start with the current month and move forward. The example I set up lists static months starting with January 2014, but that would require updating the table every month, which would be silly.

                           Thanks PhilModJunk! You're the number 1 FM Forum Guru!!!smiley

                      • 8. Re: Charting Expiration Dates
                        TylerNelson

                             So I'm continuing to work on this Project Options Chart demo file on Dropbox, so if you re-download it via the Dropbox link you should see the changes I've made. So far all I've done is update the Months table so that it dynamically increments the months based on the current month, so MonthNumber 1 starts with the current month and projects 24 months into the future for the chart. I anxiously await any nuggets of wisdom you may off via this thread at any point in the near future, cause I still feel a little hopeless about this chart :(

                             I came up with some not-so-elegant formulas to restart the month numbers at 1 after December and to increment the years properly. I'm sure there's a better way to do it. It seems to be working just fine, since we're in December, but I wouldn't be surprised if a flaw in the calculation throws things off come next month! You can feel free to see the calcs in action in the updated file. Or you can review them here:

                             Let ( 
                              
                             [ 
                             ~RecordNumber = Get (RecordNumber) ; 
                             ~CurrentMonth = Month (Get (CurrentDate))
                             ]
                             ; 
                              
                             Case (
                              
                             ~CurrentMonth + (~RecordNumber - 1) = 1;
                             1 ;
                             ~CurrentMonth + (~RecordNumber - 1) = 2;
                             2 ;
                             ~CurrentMonth + (~RecordNumber - 1) = 3;
                             3 ;
                             ~CurrentMonth + (~RecordNumber - 1) = 4;
                             4 ;
                             ~CurrentMonth + (~RecordNumber - 1) = 5;
                             5 ;
                             ~CurrentMonth + (~RecordNumber - 1) = 6;
                             6 ;
                             ~CurrentMonth + (~RecordNumber - 1) = 7;
                             7 ;
                             ~CurrentMonth + (~RecordNumber - 1) = 8;
                             8 ;
                             ~CurrentMonth + (~RecordNumber - 1) = 9;
                             9 ;
                             ~CurrentMonth + (~RecordNumber - 1) = 10;
                             10 ;
                             ~CurrentMonth + (~RecordNumber - 1) = 11;
                             11 ;
                             ~CurrentMonth + (~RecordNumber - 1) = 12;
                             12 ;
                              
                             ~CurrentMonth + (~RecordNumber - 1) = 13;
                             1 ;
                             ~CurrentMonth + (~RecordNumber - 1) = 14;
                             2 ;
                             ~CurrentMonth + (~RecordNumber - 1) = 15;
                             3 ;
                             ~CurrentMonth + (~RecordNumber - 1) = 16;
                             4 ;
                             ~CurrentMonth + (~RecordNumber - 1) = 17;
                             5 ;
                             ~CurrentMonth + (~RecordNumber - 1) = 18;
                             6 ;
                             ~CurrentMonth + (~RecordNumber - 1) = 19;
                             7 ;
                             ~CurrentMonth + (~RecordNumber - 1) = 20;
                             8 ;
                             ~CurrentMonth + (~RecordNumber - 1) = 21;
                             9 ;
                             ~CurrentMonth + (~RecordNumber - 1) = 22;
                             10 ;
                             ~CurrentMonth + (~RecordNumber - 1) = 23;
                             11 ;
                             ~CurrentMonth + (~RecordNumber - 1) = 24;
                             12 ;
                              
                             ~CurrentMonth + (~RecordNumber - 1) = 25;
                             1 ;
                             ~CurrentMonth + (~RecordNumber - 1) = 26;
                             2 ;
                             ~CurrentMonth + (~RecordNumber - 1) = 27;
                             3 ;
                             ~CurrentMonth + (~RecordNumber - 1) = 28;
                             4 ;
                             ~CurrentMonth + (~RecordNumber - 1) = 29;
                             5 ;
                             ~CurrentMonth + (~RecordNumber - 1) = 30;
                             6 ;
                             ~CurrentMonth + (~RecordNumber - 1) = 31;
                             7 ;
                             ~CurrentMonth + (~RecordNumber - 1) = 32;
                             8 ;
                             ~CurrentMonth + (~RecordNumber - 1) = 33;
                             9 ;
                             ~CurrentMonth + (~RecordNumber - 1) = 34;
                             10 ;
                             ~CurrentMonth + (~RecordNumber - 1) = 35;
                             11 ;
                             ~CurrentMonth + (~RecordNumber - 1) = 36;
                             12 
                                   ))
                              
                             And here is my more concise calculation for the Year:
                              
                                  Let ( 
                                   
                                  [ 
                                  ~RecordNumber = Get (RecordNumber) ; 
                                  ~CurrentMonth = Month (Get (CurrentDate)) ;
                                  ~CurrentYear = Year (Get (CurrentDate)) 
                                  ]
                                   
                                  ; 
                                   
                                  Case (
                                   
                                  ~CurrentMonth + (~RecordNumber - 1) ≤ 12;
                                  ~CurrentYear ;
                                   
                                  ~CurrentMonth + (~RecordNumber - 1)  ≥  13 and ~CurrentMonth + (~RecordNumber - 1)  ≤ 24;
                                  ~CurrentYear  + 1 ;
                                   
                                  ~CurrentMonth + (~RecordNumber - 1) > 24;
                                  ~CurrentYear  + 2 
                                   
                                        ))

                              

                        • 9. Re: Charting Expiration Dates
                          philmodjunk

                               If you are going to use 30 day intervals as a "month", then:

                               Round ( ( Date2 - Date1 ) / 30 ; 0 )

                               would do that.

                          • 10. Re: Charting Expiration Dates
                            TylerNelson

                                 Happy New Year PhilModJunk! So my client is still asking for this chart and I really haven't put much thought into it since the last forum post. I tried using a calculation from the context of Projects that simply calculates the difference in months (using 30-day months) between the Option expiration dates and today's date. When I chart the X-Axis based on this (from the context of Globals) I get a result for only one project (with an Initial Option expiration on 1/1/16 (27.4 months). This, of course, leaves the X axis ticks as numerical representations of the months, not the month names. I realize I'm not properly utilizing the ProjectionMonths interface table. But I'm still at a bit of a loss as to how to approach this. Perhaps you could point me in the right direction???

                                 Thanks!!!

                            • 11. Re: Charting Expiration Dates
                              philmodjunk

                                   I'm not exactly sure what is your question? What part of this are you trying to do differently?

                              • 12. Re: Charting Expiration Dates
                                TylerNelson

                                     I'd like for the X-Axis ticks to display the ProjectionMonths, i.e. January, February, March, etc. as opposed to having numbers that represent the quantity of months. But my currently calculation result simply lists the quantity of months, i.e. 27.4, 15.2, 19.2 & 18.2... For some reason using this calculation, which simply subtracts the current date from the expiration date, it is only plotting one of the projects. You can see the result in the test solution in the dropbox folder:

                                     https://www.dropbox.com/sh/ixmf6kt7j5rmytk/uRQaIrtw7S

                                • 13. Re: Charting Expiration Dates
                                  philmodjunk

                                       What data source options have you specified? Is this data in a related table or the current layout's found set?

                                  • 14. Re: Charting Expiration Dates
                                    TylerNelson

                                         Current Record Delimited Data

                                         Chart is in the context of Globals TO related to Projects via relationship defined as follows:

                                         Globals                                                         Projects

                                         zcConstant1 (Constant 1)                =      OptionedBoolean (=1 if Project has option)

                                         zcActiveStatus (constant "Active")  =      ProjectStatus ("Active" or "Inactive")

                                    1 2 Previous Next