LEMONADE STAND, PART 2: MORE FORECASTING AND TREND LINES
In Part 1 of our Lemonade Stand series, we demonstrated how to add a trend line to a chart in FileMaker 12. In this part, we’ll look at extending that technique to use a date field rather than a number field.
Let’s take a look at the final chart and then we’ll dig into the calculations used to create the chart. 
This chart was generated from the following data in the sales_date table. The only difference from the chart we produced in Part 1 is that period is defined as a date field. 
Converting dates to numbers in SQL
In order to use a date for the period, we need to convert the date to a number in each of our SQL queries. In a FileMaker calculation, we could simply use the GetAsNumber() function to convert a date to a number. To get the same result within a SQL query, we can subtract a date from our period value as follows:
Here’s the new FileMaker calculation for the Trend series in our chart:
Making the chart title dynamic
You may have noticed that our chart title includes the year. The following calculation can be used to make the title dynamic based on the dates in the period field. We simply get the minimum year and the maximum year and display them appropriately as part of the title.
Labeling the months
One last thing to note about the chart is that the x-axis labels are displaying the month name rather than the date. To achieve this, simply specify the x-axis data as follows: 
Coming soon
In Part 1, we mentioned that sales for our lemonade stand are likely to be seasonal. This may be true for the data you’re working with as well. In Part 3, we’ll take a look at how to calculate a monthly seasonal index from previous years’ data. Here’s what that seasonal index might look like: 
The seasonal index tells us that historically our sales in March are only about 40% of our monthly average for the year. Likewise, sales in August have been about 190% of the average. We’ll also look at how to combine the seasonal index with the trend to create a new seasonal forecast. The following image shows the chart we’ll create using the historical sales data from the previous three years: 
While our lemonade stand data has been carefully crafted to illustrate the concepts of trends and seasonality, real-world data rarely makes things so clear. However, the methods we’re exploring in these articles can help to tell an objective story about a set of data and provide a basis for forecasting future results. We’re working on an example file that covers all of the techniques for this series. If you’d like to get a copy of the example file or if you’ve found this article useful and would like to see the series continue, please let us know at info@skeletonkey.com. Greg Lane is VP of Application Development at Skeleton Key and a FileMaker Certified Developer. About Skeleton Key Skeleton Key is an accomplished team of technology consultants who solve business problems. We specialize in the rapid development of custom applications, integrating Macs and PCs in the professional workplace, and providing personalized training. Despite our end-to-end technical skills, we are consultant first and technologist second. We know that you don’t just need technology. You need to know that the technology you choose to deploy will provide the results you desire. Skeleton Key is a Platinum Level FileMaker Business Alliance company, an Authorized FileMaker Trainer, a member of the Apple Consultants Network and a Microsoft Registered Partner.