# 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:

period – DATE '0001-01-01' + 1

Here’s the new FileMaker calculation for the Trend series in our chart:

Let( [ n = ExecuteSQL("SELECT COUNT(sales) FROM sales_date";"";""); sum_xy = ExecuteSQL("SELECT SUM((period – DATE '0001-01-01' + 1)*sales) FROM sales_date WHERE sales IS NOT NULL";"";""); sum_x = ExecuteSQL("SELECT SUM((period – DATE '0001-01-01' + 1)) FROM sales_date WHERE sales IS NOT NULL";"";""); sum_y = ExecuteSQL("SELECT SUM(sales) FROM sales_date WHERE sales IS NOT NULL";"";""); sum_xx = ExecuteSQL("SELECT SUM((period – DATE '0001-01-01' + 1)**2) FROM sales_date WHERE sales IS NOT NULL";"";""); avg_y = ExecuteSQL("SELECT AVG(sales) FROM sales_date WHERE sales IS NOT NULL";"";""); avg_x = ExecuteSQL("SELECT AVG((period – DATE '0001-01-01' + 1)) FROM sales_date WHERE sales IS NOT NULL";"";""); B = (sum_xy – (n* avg_x * avg_y)) / (sum_xx – n * avg_x^2); A = avg_y – (B * avg_x); Y = A + (B * GetAsNumber(sales_date::period)) ]; Round( Y;2 ) )

## 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.

Let([ minYear = ExecuteSQL("SELECT MIN(YEAR(period)) FROM sales_date";"";""); maxYear = ExecuteSQL("SELECT MAX(YEAR(period)) FROM sales_date";"";""); years = Case(minYear = maxYear; minYear; minYear & "–" & maxYear) ]; years & " Sales by Month" )

## 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: