Excel: do not be fooled by Excel trend lines

Something I see quite often is blind faith in Excel trend lines. Somebody collates some time based data, creates a graph in Excel and then ...Add Trendline.

Sadly these trends can be significantly wrong as easily as they can be right.

One illustration of this is provided by the datasets crafted by Francis Anscombe known as ‘Anscombe’s Quartet’ (see Graphs in Statistical Analysis by F.J.Anscombe. American Statistician. 27 (February1973): 17-21)

The summary statistics for the four datasets (A, B, C, D) are identical:

But plotting the datasets shows that the four are really completely different

Now add Excel trendlines:




















It is clear from the illustrations that three out of the four trendlines are completely wrong

Putting this another way: our visual inspection of the patterns is far more reliable than Excel's automatic calculation. We should trust ourselves.

In his book 'Show Me the Numbers: Designing Tables and Graphs to Enlighten' (Second Edition. Analytics Press. 2012, pp.224-8), Stephen Few shows  how the trend shown can be affected significantly by the small differences in range of values selected.

So there are two big things to watch out for - the range of data that you decide to trend and the way Excel then attempts to fit a trend line

T-SQL: Calculating with Integers

T-SQL is Transact SQL which is the version of SQL used with Microsoft SQL Server

This brief note explains a problem you may encounter when doing calculations with integers - and how to solve it

Take two integers and divide one by the other, then multiply by 100 to express as a percentage

Say 949 divided by 1001 multiplied by 100

The result expressed to two decimal places is 94.81  (%)

Now try this in T-SQL as follows:

DECLARE @Percentage as DECIMAL(5,2)
DECLARE @intNumerator as INT =949
DECLARE @intDemoninator  as INT = 1001

SET @Percentage=100* (@intNumerator/@intDemoninator)
SELECT  @Percentage

The result will be 0.00

So what is going wrong?

The 'problem' is with the integers. The result of a calculation using two integers will itself be an integer. It rounds the result down to the nearest whole number - in this case 0.9481 rounds down to zero

SELECT 949/1001  gives 0

And 100 * 0 is still 0

Had we swapped the numerator and denominator around the result, being greater than 1 would have rounded to 1

SELECT 1001/949 gives 1


What is the solution?

The solution is to convert the integers to the float data type before attempting the calculation
Everything works smoothly then

DECLARE @Percentage as DECIMAL(5,2)
DECLARE @intNumerator as INT =949
DECLARE @intDemoninator  as INT = 1001

SET @Percentage=100* (cast(@intNumerator as float)/cast (@intDemoninator as float))
SELECT  @Percentage

gives  94.81


If you read internet problem solving sites, the cognoscenti like to tell the novices that this is 'known behaviour' implying, of course, that they would never ever have made this mistake

Yea!

T-SQL: Calculating some common relative dates

T-SQL is 'Transact-SQL', the version of SQL used with Microsoft SQL Server. This SQL code may be adaptable for other versions of SQL.

What is covered in this article

There are some things I need to find quite often when working with dates, such as the start date of the current month the start date of the current quarter the start date of the current financial year the end date of the current month the end date of the current quarter the end date of the current financial year how many days have elapsed and how many days remaining in the current month This article shows ways to calculate all of these using T-SQL. It then shows a version of  a User Defined Function which can be used for calculating these relative dates based on any input date.

Start date of the current month

The following will return the date of the start of the current month:

SELECT DATEADD(m, DATEDIFF(m, 0, GetDate()), 0)

It works in two steps:

First the DATEDIFF() counts how many whole months between now and the SQL zero date (the base datetime 1900-01-01 00:00:00.000)

Then the DATEADD()  adds that number of months back onto the zero date

Start date of the next month

The method above can be adapted to give the Start Date of the Next Month by increasing the number of months to add by 1

SELECT DATEADD(m, DATEDIFF(m, 0, GetDate())+1, 0)

Other similar offsets can be calculated by altering the +1 accordingly

End date of the current month

Versions from SQL Server 2012 have the built in function EOMONTH() for calculating the date of the end of the month based on any given input date. For example

SELECT EOMONTH(Getdate())

 returns   2017-12-31

This is fine if all you want is a Date. But I often need the result as a DateTime when I am working with time based transaction data. And EOMONTH() is not available if you have a version older than SQL Server 2012

One way to derive the End Date of the Current Month is to work from the Start Date of the Next Month (as calculated above) and then deduct one second from it (Generally I only need this to be to the nearest second -  and remembering that deducting is Adding a negative number):

DATEADD(s,-1,<End Date of Current Month>)

SELECT DATEADD(s,-1,(DATEADD(m, DATEDIFF(m, 0, GetDate())+1, 0)))

We could alternatively work from the Start Date of the Current Month (as calculated above). This method adds one month onto the start date - to give the start of the following month - and then deducts one second from it

DATEADD(s,-1,(DATEADD(m,1,<Start Date of Current Month>)))

This method would be ok if we had already calculated and stored the value of <Start Date of Current Month>. But otherwise it is less efficient in a single expression as it ends up doubling the number of DATEADDs of months:

SELECT DATEADD(s,-1,(DATEADD(m,1,DATEADD(m, DATEDIFF(m, 0, GetDate()), 0))))

So we would simplify these and end up with the same code as we had already derived above from First Date of Next Month

Number of days completed in the current month

A quick way to do this is to use DATEPART()  and retrieve the day part of the date

SELECT DATEPART(d, GetDate())

This works ok for situations when it is just number of days elapsed within one month.

A more generic approach is to calculate the number of days using DATEDIFF()

 DATEDIFF(d, <Reference Start Date>, <Reference As At Date>)

This method would allow us to calculate to any starting point (such as a quarter or financial year). But it needs the adjustment of adding one day to compensate for the fact that when using DATETIME the reference date (e.g. today)  will never be complete

 DATEDIFF(d, <Reference Start Date>, <Reference As At Date>)+1

So using this method the following is another method to calculate the number of days elapsed within the current month.

SELECT DATEDIFF(d, DATEADD(m, DATEDIFF(m, 0, GetDate()), 0),GetDate())+1

Number of days remaining in the current month

This can be calculated using a variation of the previous method, in generic form:

 DATEDIFF(d,  <Reference As At Date>, <Reference End Date>)

This can be set out as follows to give days remaining in the current month

SELECT DATEDIFF(d,Getdate(), DATEADD(s,-1,(DATEADD(m, DATEDIFF(m, 0, GetDate())+1, 0))))

Start date of the current quarter

This can be calculated using the same method used above for calculating Start Date of Current Month. The only change is the time interval (set to q for quarter instead of m for month)

SELECT DATEADD(q, DATEDIFF(q, 0, GetDate()), 0)

End date of the current quarter

This can be calculated using the same method used above for calculating End Date of Current Month. The only change is the time interval (set to q for quarter instead of m for month)
SELECT DATEADD(s,-1,(DATEADD(q, DATEDIFF(q, 0, GetDate())+1, 0)))

Number of days completed in the current quarter

This can be calculated using the method set out above adjusted for quarters

DATEDIFF(d, <Reference Start Date>, <Reference As At Date>)

SELECT DATEDIFF(d, DATEADD(q, DATEDIFF(q, 0, GetDate()), 0),GetDate())+1

Number of days remaining in the current quarter

This can also be calculated using the method set out above for months, adjusted for quarters

DATEDIFF(d,  <Reference As At Date>, <Reference End Date>)

SELECT DATEDIFF(d,Getdate(), DATEADD(s,-1,(DATEADD(q, DATEDIFF(q, 0, GetDate())+1, 0))))

Start date of the current financial year

The start of the financial year in any analyses I have had to do so far has always been the 1st April. Things can be more complicated if the financial year is taken to start on 5th April or from a different month. Ignoring these possible complications for now, for me finding the start of the financial year simply means rolling back to the most recent previous 1st April from any given reference date.

The method I have devised uses DATEPART() to find the month number for the reference (current) month.The difference between this and 4 (the month number for April) is then the number of months to roll back.

There is a complication though. For months prior to April, this method would create a negative number with the result the date would roll forward not back.

So we need to test for whether the month number of our reference month is less than 4 and if so use a modified calculation in which we deduct an additional 12 months.

I have done this using a case statement

 DATEADD(m,

(CASE WHEN  DATEPART(m,<Reference As At Date>)<4 THEN 4-DATEPART(m,<Reference As At Date>)-12

ELSE 4-DATEPART(m,<Reference As At Date>) END)

,<Reference Month Start Date>)

This logic is not complicated, but it does look complicated when developed into a single standalone select statement

 SELECT DATEADD(m,

(CASE WHEN  DATEPART(m,GetDate())<4 THEN 4-DATEPART(m,GetDate())-12

ELSE 4-DATEPART(m,GetDate()) END)

,DATEADD(m, DATEDIFF(m, 0, GetDate()), 0))

A small simplification is possible by combining the +4 and -12 in the first part to give -8

 SELECT DATEADD(m,

(CASE WHEN  DATEPART(m,GetDate())<4 THEN -DATEPART(m,GetDate())-8

ELSE 4-DATEPART(m,GetDate()) END)

,DATEADD(m, DATEDIFF(m, 0, GetDate()), 0))

Start date of the next financial year

The start date of the next financial year can be obtained by adding 12 months onto the calculation of start of current financial year

 SELECT DATEADD(m,

(CASE WHEN  DATEPART(m,GetDate())<4 THEN 4-DATEPART(m,GetDate())-12+12

ELSE 4-DATEPART(m,GetDate()) +12 END)

,DATEADD(m, DATEDIFF(m, 0, GetDate()), 0))

which simplifies slightly to

 SELECT DATEADD(m,

(CASE WHEN  DATEPART(m,GetDate())<4 THEN 4-DATEPART(m,GetDate())

ELSE 16-DATEPART(m,GetDate())  END)

,DATEADD(m, DATEDIFF(m, 0, GetDate()), 0))

End date of the current financial year

The end date of the current financial year can be calculated by deducting one second from the start of the next  financial year

 SELECT DATEADD(s,-1,DATEADD(m,

(CASE WHEN  DATEPART(m,GetDate())<4 THEN 4-DATEPART(m,GetDate())

ELSE 16-DATEPART(m,GetDate())  END)

,DATEADD(m, DATEDIFF(m, 0, GetDate()), 0)))

A script which calculates all of these as memory variables

You can adapt the following script to meet different requirements. Using variables makes the calculations a little easier to follow. It also allows a generic 'As At Date' to be set.

-------------------------------------------------
-- Date calculations -- sample SQL code
-------------------------------------------------
---------------------------------------------
-- Declare all variables
---------------------------------------------
DECLARE @AsAtDate as DATETIME -- As At Date
DECLARE @MONCurrentStartDate as DATETIME -- Start Date for As At Date's Current Month
DECLARE @MONCurrentEndDate as DATETIME -- End Date for As At Date's Current Month
DECLARE @MONCurrentDaysCompleted as INT =0 -- Numbers of days completed in Current Month
DECLARE @MONCurrentDaysLeft as  INT =0 -- Numbers of days remaining in Current Month
DECLARE @MONNextStartDate as DATETIME -- Start Date for Month following As At Date

DECLARE @QTRCurrentStartDate as DATETIME -- Start Date for As At Date's Current Quarter
DECLARE @QTRCurrentEndDate as DATETIME -- End Date for As At Date's Current Quarter
DECLARE @QTRCurrentDaysCompleted as  INT =0 -- Numbers of days completed in Current Quarter
DECLARE @QTRCurrentDaysLeft as  INT =0 -- Numbers of days remaining in Current Quarter
DECLARE @QTRNextStartDate as DATETIME -- Start Date for Quarter following As At Date

DECLARE @FYRCurrentStartDate as DATETIME -- Start Date for As At Date's Current Financial Year
DECLARE @FYRCurrentEndDate as DATETIME -- End Date for As At Date's Current Financial Year
DECLARE @FYRCurrentDaysCompleted as  INT =0 -- Numbers of days completed in Current Financial Year
DECLARE @FYRCurrentDaysLeft as  INT =0 -- Numbers of days remaining in Current Financial Year
DECLARE @FYRNextStartDate as DATETIME -- Start Date for Financial Year following As At Date

------------------------------------------------------------------------------
-- Set As At Date
------------------------------------------------------------------------------
--SET @AsAtDate = dateadd(d, -1,GETDATE()) -- Set As At Date to yesterday
SET @AsAtDate='1997-03-12' -- Set As At Date to any date to test

----------------------------------------------------------------------------
-- Calculate Month variations (for As At Date)
----------------------------------------------------------------------------
SET  @MONCurrentStartDate = DATEADD(m, DATEDIFF(m, 0, @AsAtDate), 0)
SET  @MONCurrentEndDate=DATEADD(s,-1,(DATEADD(m,1,@MONCurrentStartDate)))
SET  @MONCurrentDaysCompleted=DATEDIFF(d, @MONCurrentStartDate,@AsAtDate)+1
SET  @MONCurrentDaysLeft=DATEDIFF(d,@AsAtDate, @MONCurrentEndDate)
SET  @MONNextStartDate=DATEADD(m, DATEDIFF(m, 0, @AsAtDate)+1, 0)

-------------------------------------------------------------------------------
-- Calculate Quarter variations (for As At Date)
-------------------------------------------------------------------------------
SET  @QTRCurrentStartDate = DATEADD(q, DATEDIFF(q, 0, @AsAtDate), 0)
SET  @QTRCurrentEndDate=dateadd(s,-1,(dateadd(q,1,@QTRCurrentStartDate)))
SET  @QTRNextStartDate= DATEADD(q, DATEDIFF(q, 0, @AsAtDate)+1, 0)
SET  @QTRCurrentDaysCompleted=datediff(d, @QTRCurrentStartDate,@AsAtDate)+1
SET  @QTRCurrentDaysLeft=DATEDIFF(d,@AsAtDate, @QTRCurrentEndDate)

-------------------------------------------------------------------------------
-- Calculate Financial Year variations (for As At Date)
-------------------------------------------------------------------------------
SET  @FYRCurrentStartDate=
DATEADD(m, (case when datepart(m,@AsAtDate)<4 then 4-datepart(m,@AsAtDate)-12 --adjust for Jan to Mar to count back
else 4-DATEPART(m,@AsAtDate) end),@MONCurrentStartDate)

SET  @FYRNextStartDate=
DATEADD(m,(CASE WHEN DATEPART(m,@AsAtDate)<4 THEN 4-DATEPART(m,@AsAtDate)
ELSE 16-DATEPART(m,@AsAtDate) END),@MONCurrentStartDate)

SET  @FYRCurrentEndDate=DATEADD(s,-1,(DATEADD(yy,1,@FYRCurrentStartDate)))
SET  @FYRCurrentDaysCompleted=DATEDIFF(d, @FYRCurrentStartDate,@AsAtDate)+1
SET  @FYRCurrentDaysLeft=DATEDIFF(d,@AsAtDate, @FYRCurrentEndDate)

---------------------
-- Display results
--------------------
SELECT @AsAtDate as [As At],
@MONCurrentStartDate as [Current Month Start],
@MONCurrentEndDate as [Current Month End],
@MONCurrentDaysCompleted as [Current Month Days Completed],
@MONCurrentDaysLeft as [Current Month Days Left],
@MONNextStartDate as [Next Month Start]

SELECT @AsAtDate as [As At],
@QTRCurrentStartDate as [Current Quarter Start],
@QTRCurrentEndDate as [Current Quarter End],
@QTRCurrentDaysCompleted as [Current Quarter Days Completed],
@QTRCurrentDaysLeft as [Current Quarter Days Left],
@QTRNextStartDate as [Next Quarter Start]

SELECT @AsAtDate as [As At],
@FYRCurrentStartDate as [Current Fin Year Start],
@FYRCurrentEndDate as [Current Fin Year End],
@FYRCurrentDaysCompleted as [Current Fin Year Days Completed],
@FYRCurrentDaysLeft as [Current Fin Year Days Left],
@FYRNextStartDate as [Next Fin Year Start]

You can run the script above to test the results of the calculations. In practice you will only need to use some elements of it. But if you keep the overall script you can dip into it and lift the elements you need for any new SQL script.

User-defined Functions for handling relative dates

If you use date calculations such as these a lot, it may be worth creating some User Defined Functions. You could create individual functions for each measure. Or you could create a single function which combines them all. The following code is one version of a multi-purpose function. As input it takes the 'As At Date' and a thee letter code indicating what date to return

---------------------------------------------------------------------------------------

-- Function RelativeDate ()

---------------------------------------------------------------------------------------

-- E.Bolton. Last updated 26/12/2017

---------------------------------------------------------------------------------------

-- Takes any given date @AsAtDate

-- and a three-character return type code @ReturnCode

-- which indicates what to return

-- Returns the relative date

-- Inputvalues for @ReturnCode

--              MCS indicates return Start Date for As At Date's Current Month  

--              MCE indicates return End Date for As At Date's Current Month  

--              MNS indicates return Start Date for Month following As At Date

--              QCS indicates return Start Date for As At Date's Current Quarter  

--              QCE indicates return End Date for As At Date's Current Quarter 

--              QNS indicates return Start Date for Quarter following As At Date

--              FYS indicates return Start Date for As At Date's Current Financial Year

--              FYE   indicates return End Date for As At Date's Current Financial Year 

--              FYN indicates return Start Date for Financial Year following As At Date

---------------------------------------------------------------------------------------

CREATE FUNCTION [dbo].[RelativeDate]

--alter FUNCTION [dbo].[RelativeDate]

  ( @AsAtDate datetime, @ReturnCode Varchar(3))

RETURNS DATETIME

AS

BEGIN

DECLARE @ReturnDate as DATETIME     

DECLARE @MONCurrentStartDate as DATETIME        -- Start Date for As At Date's Current Month  MCS

DECLARE @MONCurrentEndDate as DATETIME -- End Date for As At Date's Current Month  MCE

DECLARE @MONNextStartDate as DATETIME    -- Start Date for Month following As At Date MNS

DECLARE @QTRCurrentStartDate as DATETIME   -- Start Date for As At Date's Current Quarter  QCS

DECLARE @QTRCurrentEndDate as DATETIME                  -- End Date for As At Date's Current Quarter QCE

DECLARE @QTRNextStartDate as DATETIME                    -- Start Date for Quarter following As At Date QNS

DECLARE @FYRCurrentStartDate as DATETIME         -- Start Date for As At Date's Current Financial Year FYS

DECLARE @FYRCurrentEndDate as DATETIME                   -- End Date for As At Date's Current Financial Year FYE 

DECLARE @FYRNextStartDate as DATETIME                     -- Start Date for Financial Year following As At Date FYN

----------------------------- 

 -- Date Calculations

-----------------------------

SET @MONCurrentStartDate =DATEADD(m, DATEDIFF(m, 0, @AsAtDate), 0) 

SET @MONCurrentEndDate=DATEADD(s,-1,(DATEADD(m,1,@MONCurrentStartDate))) 

SET @MONNextStartDate=DATEADD(m, DATEDIFF(m, 0, @AsAtDate)+1, 0)  

SET @QTRCurrentStartDate = DATEADD(q, DATEDIFF(q, 0, @AsAtDate), 0) 

SET @QTRCurrentEndDate=DATEADD(s,-1,(DATEADD(q,1,@QTRCurrentStartDate)))

SET @QTRNextStartDate= DATEADD(q, DATEDIFF(q, 0, @AsAtDate)+1, 0) 

SET @FYRCurrentStartDate=

 DATEADD(m, (case when DATEPART(m,@AsAtDate)<4 then 4-DATEPART(m,@AsAtDate)-12 --adjust for Jan to Mar to count back

 else 4-DATEPART(m,@AsAtDate) end),@MONCurrentStartDate)

SET @FYRCurrentEndDate=DATEADD(s,-1,(dateadd(yy,1,@FYRCurrentStartDate)))

SET @FYRNextStartDate= 

DATEADD(m,(CASE WHEN  DATEPART(m,@AsAtDate)<4 THEN 4-DATEPART(m,@AsAtDate)

ELSE 16-DATEPART(m,@AsAtDate)  END),@MONCurrentStartDate)

--------------------------------------------------

-- Set @ReturnDate based on input code @ReturnCode

--------------------------------------------------

SET @ReturnDate =

(Case when @ReturnCode='MCS' then  @MONCurrentStartDate

when @ReturnCode='MCE' then @MONCurrentEndDate

when @ReturnCode='MNS' then @MONNextStartDate

when @ReturnCode='QCS' then @QTRCurrentStartDate

when @ReturnCode='QCE' then @QTRCurrentEndDate

when @ReturnCode='QNS' then @QTRNextStartDate

when @ReturnCode='FYS' then @FYRCurrentStartDate

when @ReturnCode='FYE' then @FYRCurrentEndDate

when @ReturnCode='FYN' then @FYRNextStartDate

end)

RETURN @ReturnDate 

END

GO

There are various ways that the function above could have been constructed. I have chosen to calculate all the variations and then return the required one. It could be argued that this expends unnecessary resources each time. Alternative versions could be designed with more careful conditional logic. But the code above works ok and is not slow. And some of the dates calculated are used to calculate some of the others

Choices

You can use single, self-contained expressions which calculate the values whenever you need them. This has the advantage of completeness and portability. And when debugging, the source of a problem is easier to locate. But you will end up writing fairly long expressions which are hard to follow and which can get messed up easily

You may find that calculating the values and retaining these in memory variables makes things easier to manage. This may work well if you need to use the same values several times in the SQL script. But in simple scripts, it probably over-complicates things.

You could create a parameter driven User defined Function (along the lines of the one illustrated here) and then call it from all your subsequent scripts. This has the advantage of economy but you will need to ensure that the function has been set up on any database instances on which you want to run it

You could create individual User defined Functions for each type of calculation. Apart from the initial headache of doing this, it will provide a useful palette of options.

As with all these things, inevitably any of this could be done better (more efficiently, made more compact, made more generic, use some ingenious algorithm that will enhance your reputation)

How far you go in tuning and fine polishing SQL scripts is up to you.

I have had to overcome a tendency towards the paralysis of perfectionism by using a 'fit for purpose test'. It goes something like this: Question 1: Does it work? If Yes move on to Question 2 Question 2: Does it take forever to run (or too long for its context)? If No move on the Question 3 Question 3: Does it crash the server, lock up any key applications, or dim the lights in the building?  If No then move on to Question 4 Question 4: I have a limited amount of lifetime resource. Do I want to spend some of it making this thing that already works ok work even better? Or do I want to use it to solve one of the many other problems I still need to deal with? Move on to the next challenge.

Before you go

You might want to check out the following 

https://stackoverflow.com/questions/691022/how-to-determine-the-number-of-days-in-a-month-in-sql-server