Solved

Lag analysis

Posted on 2008-06-17
18
1,243 Views
Last Modified: 2013-11-27
I need to create a lag analysis for 3, 6, and 12 months.  For example, I need to take the CurrentDolrs/EndingGrossAR from 12 months ago
What would be the best way to do this?  Query and/or Function?
0
Comment
Question by:nfstrong
  • 9
  • 5
  • 3
18 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
A query can work just fine, but to get more than generalities you will need to post your schema.
0
 

Author Comment

by:nfstrong
Comment Utility
Currently I have fields in a table (tblAVSFSNIDCalc) storing the calculations and a function being called to perform them.  The result is then stored in a separate table (tblAVSFSNID) for use in reports.
Is that what you're looking for?
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
nfstrong said:
>>Is that what you're looking for?

Not quite.  It doesn't really help me understand what you are trying to do, and what you have to work with.
0
 

Author Comment

by:nfstrong
Comment Utility
OK,
I have a table with the following data: (there is more data, but just to simplify)

ClientNumber     DateOfData     31PlusDelq     EndingGrossAR
ABC0                  1/31/2007             500                  12000
ABC0                  2/28/2007             350                  12500
.
.
.
ABC0                   5/31/2008            200                   15000

To find the 31PlusDelqLag for 12 months I need to take the 31PlusDelq value for 5/31/2008 divided by the EndgingGrossAR from 12 months prior or 5/31/2007.  Same for the 3 and 6 months lag.  It would be EndingGrossAR from 3 months prior and 6 months prior.
Let me know if you need more.
0
 

Author Comment

by:nfstrong
Comment Utility
Any thoughts?
0
 

Author Comment

by:nfstrong
Comment Utility
Anyone there?
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
nfstrong,

My apologies.  I doubt I am going to be able to reengage in this one.  I suggest you use the "request attention" link at the top of the question, and ask a Moderator to see if additional Experts can be enticed to join in.

Sorry, and good luck with your project,

Patrick
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
nfstrong,

Do you need to base this calculation on a single row of data out of your original table, or does the calculation need to be based on a sum (or other aggregate) of records in a given time frame?  

For example, if I ran a 12-month lag analysis on June 30 2008, should that calculation be based on a single record from June 30 2007?  Or a collection of data between June 30 2007 and June 30 2008?

Can you post some sample data based on the table in your last post showing what your expected results are?
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:nfstrong
Comment Utility
Here is some sample data with expected results.
                                                                                                           31PlusDelqLag
ClientNumber     DateOfData     31PlusDelq     EndingGrossAR  12 mo   6 mo    3 mo
ABC0                  5/31/2007             500                  12000            
ABC0                  11/30/2007           350                  12500
ABC0                  2/29/2008             300                  14000
.
.
ABC0                   5/31/2008            200                   15000        1.67%  1.6% 1.43%
0
 

Author Comment

by:nfstrong
Comment Utility
This calculation is taking 31PlusDelq from 5/31/2008 divided by EndingGrossAR from 5/31/2007 for the 12 mo lag.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
Comment Utility
You could use a function to look up the value of EndingGrossAR at the required interval, and return 31PlusDelq/EndingGrossAR.

The same function (air-code in code snippet) could be used for 3, 6 and month lags, if you supply the interval as a parameter.  You would have to return a null as a special case if no record is found at the given interval.

The function can be called from VBA as follows (txt31PlusDelq is a text box on the form bound to the 31PlusDelq field):

Lag12Mo = GetLag(Me.txt31PlusDelq, -12)
Lag6Mo = GetLag(Me.txt31PlusDelq, -6)
etc.

You can also call the function from a control source or query.  

You will also need to handle zeros in EndingGrossAR as special cases if needed to avoid divide by zero errors.

Calls to VBA functions can dramatically slow down queries, but I can't think of a way to do this with straight SQL.

 
Function GetLag(lngCurr31PlusDelq as Long, dtCurrDate as Date, intLagInterval as integer) as Variant       

          dim rs as dao.recordset

          dim strSQL as string

 

          ' This query selects the EndingGrossAR at the interval passed to the function

          strSQL = "SELECT EndingGrossAR FROM YourTable WHERE datediff('m', #" & dtCurrDate & "#, DateofData) = " & intLagInterval

          set rs = currentdb.openrecordset(strSQL)

         

          ' Return Null if no records exist at given interval.

          if rs.recordcount = 0 then

               GetLag = Null

               Exit Function

          End if

 

          getLag = lngCurr31PlusDelq/rs!EndingGrossAR    '<------ Return EndingGrossAR/ EndingGrossAR  at the specified interval

 

End Function

Open in new window

0
 

Author Comment

by:nfstrong
Comment Utility
mbizup,
I have incorporated your code into my already existing function, but am getting Null when I should have a value returned.  I have data entered in my tables for all of 2007.  When I go to enter 1/31/2008 data and run the below function I get no record returned.  Is something missing?
Public Function Thirty1PlusDelqLag12monthsReturnCalc(ByVal ClientNumber As String, ByVal DateOfData As Date, ByVal Thirty1PlusDelqLag12monthsCalc As String) As Variant

    Dim db As DAO.Database

    Dim rs As DAO.Recordset

    Dim rs1 As DAO.Recordset

    Dim strSQL As String

    Set db = CurrentDb

    

    If Thirty1PlusDelqLag12monthsCalc <> "" And IsNull(Thirty1PlusDelqLag12monthsCalc) = False Then

    

        strSQL = "SELECT " & Thirty1PlusDelqLag12monthsCalc & " " & _

                 "FROM tblAVSFSNID " & _

                 "WHERE tblAVSFSNID.ClientNumber = '" & ClientNumber & "' " & _

                 "AND tblAVSFSNID.DateOfData = #" & DateOfData & "#"

        Set rs = db.OpenRecordset(strSQL)

        Thirty1PlusDelqLag12monthsReturnCalc = rs.Fields(0)

        rs.Close

        Set rs = Nothing

    Else

        'Thirty1PlusDelqLag12monthsReturnCalc = GetLag([tblAVSFSNID].[Thirty1PlusDolrs], DateOfData, -12)

        strSQL = "SELECT EndingGrossAR " & _

                 "FROM tblAVSFSNID " & _

                 "WHERE tblAVSFSNID.ClientNumber = '" & ClientNumber & "' " & _

                 "AND tblAVSFSNID.DateOfData = #" & DateOfData & "# " & _

                 "AND DATEDIFF('m', #" & DateOfData & "#, DateofData) = -12"

        Set rs = db.OpenRecordset(strSQL)

          

          If rs.RecordCount = 0 Then

               Thirty1PlusDelqLag12monthsReturnCalc = Null

               Exit Function

          End If
 

        strSQL = "SELECT 31PlusDolrs " & _

                 "FROM tblAVSFSNID " & _

                 "WHERE tblAVSFSNID.ClientNumber = '" & ClientNumber & "' " & _

                 "AND tblAVSFSNID.DateOfData = #" & DateOfData & "#"

        Set rs1 = db.OpenRecordset(strSQL)

        Thirty1PlusDelqLag12monthsReturnCalc = rs1![31PlusDolrs] / rs!EndingGrossAR

    End If

        'This will save the value back to the table

        strSQL = "UPDATE tblAVSFSNID " & _

                 "SET 31PlusDelqLag12months =" & Thirty1PlusDelqLag12monthsReturnCalc & " " & _

                 "WHERE tblAVSFSNID.ClientNumber = '" & ClientNumber & "' " & _

                 "AND tblAVSFSNID.DateOfData = #" & DateOfData & "#"

        CurrentDb.Execute strSQL, dbFailOnError
 

        rs.Close

        Set rs = Nothing

        rs1.Close

        Set rs1 = Nothing

        'db.Close

        Set db = Nothing
 

End Function

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
nfstrong,

Here are some possible problems...

- The fieldnames in the code you posted are not the same as the field names in the sample table you posted earlier.  My code is based on the field names that you originally posted, so make sure you are using the right field names in your implementation of this function.

- The code I posted uses "YourTable" as a generic table name.  This should be replaced with your actual table name

- It looks like your code is attempting to store the results of the GetLag function in your table (I believe you are getting nulls because the call to GetLag is commented out).  

- As a rule you should not store calculated data like this in your tables, but calculate it in queries and display it in reports, forms etc on an as-needed basis.  


Try this...

-The query in the code snippet below calls the getLag function to calculate the lag.  

- I've also attached a sample .mdb file that includes this query and the GetLag function from my last post.  It is based on the data that you posted earlier in this thread.  

- To work this into your own database, you'd need to change field and table names to reflect the actual names in your database.


Take a look at the query results in the sample...
SELECT 

 

ClientNumber, 

DateOfData,

[31PlusDelq], 

EndingGrossAR, 

GetLag([31plusdelq],[dateofdata],-12) AS [12 Month Lag], 

GetLag([31plusdelq],[dateofdata],-6) AS [6 Month Lag], 

GetLag([31plusdelq],[dateofdata],-3) AS [3 Month Lag]

 

FROM YourTable;

Open in new window

lag.mdb
0
 

Author Comment

by:nfstrong
Comment Utility
I've changed the query in my function, but am getting Error 2015 as the value from the GetLag function.  I'm not sure what that is.
Public Function Thirty1PlusDelqLag3monthsReturnCalc(ByVal ClientNumber As String, ByVal DateOfData As Date, ByVal Thirty1PlusDelqLag3monthsCalc As String) As Variant

    Dim db As dao.Database

    Dim rs As dao.Recordset

    Dim rs1 As dao.Recordset

    Dim strSQL As String

    Set db = CurrentDb

    ClientNumber = "ABC0"

    DateOfData = "12/31/2007"

    

    If Thirty1PlusDelqLag3monthsCalc <> "" And IsNull(Thirty1PlusDelqLag3monthsCalc) = False Then

    

        strSQL = "SELECT " & Thirty1PlusDelqLag3monthsCalc & " " & _

                 "FROM tblAVSFSNID " & _

                 "WHERE tblAVSFSNID.ClientNumber = '" & ClientNumber & "' " & _

                 "AND tblAVSFSNID.DateOfData = #" & DateOfData & "#"

        Set rs = db.OpenRecordset(strSQL)

        Thirty1PlusDelqLag3monthsReturnCalc = rs.Fields(0)

    rs.Close

    Set rs = Nothing

    

    Else

        strSQL = "SELECT tblAVSFSNID.ClientNumber, tblAVSFSNID.DateOfData, tblAVSFSNID.[31PlusDolrs], tblAVSFSNID.EndingGrossAR, GetLag([31PlusDolrs],[DateOfData],-3) AS [3 month lag] " & _

                 "FROM tblAVSFSNID;"

        Set rs = db.OpenRecordset(strSQL)

        Debug.Print strSQL

        Thirty1PlusDelqLag3monthsReturnCalc = [3 month lag]

        Debug.Print [3 month lag]

    End If

        'This will save the value back to the table

        strSQL = "UPDATE tblAVSFSNID " & _

                 "SET 31PlusDelqLag3months =" & Thirty1PlusDelqLag3monthsReturnCalc & " " & _

                 "WHERE tblAVSFSNID.ClientNumber = '" & ClientNumber & "' " & _

                 "AND tblAVSFSNID.DateOfData = #" & DateOfData & "#"

        CurrentDb.Execute strSQL, dbFailOnError
 

        rs.Close

        Set rs = Nothing

        rs1.Close

        Set rs1 = Nothing

        'db.Close

        Set db = Nothing
 

End Function

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
nfstrong,

What specific line of code do you get this error on?   Also, what is the actual error message you are getting?

When the error message box pops up, click "debug".  Copy and paste the highlighted code here.

> Thirty1PlusDelqLag3monthsReturnCalc = [3 month lag]

The syntax for referring to a field in a VBA recordset is different from the syntax for referring to a field within an SQL statement.  In SQL, you can use the field name alone.  In VBA, you have to refer to the recordset as well as the field name like this:

Thirty1PlusDelqLag3monthsReturnCalc = rs![3 month lag]
                                                                               ^--- Use the recordset name as a prefix
0
 

Author Comment

by:nfstrong
Comment Utility
I added the recordset to refer to the field and that fixed my problem.  Thanks for all of your help and patience.  I really appreciate it!
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
nfsstrong,

Glad to help out.

But I'm curious how you're using the data that you are saving to the table.  Revisit this comment (and the sample DB):
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23492961.html#21948967

A query can be used to calculate (and display) data at any time without actually saving the derived data to a table.  In general, storing derived data in tables is redundant and can cause problems later on if any of the data behind the calculations changes.

If you are interested in or need help with using a query to display the lag calculations in forms or reports, post another question with specifics on how you want to see this data presented.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now