?
Solved

Lag analysis

Posted on 2008-06-17
18
Medium Priority
?
1,295 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
  • 3
18 Comments
 
LVL 93

Expert Comment

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

Author Comment

by:nfstrong
ID: 21812795
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 93

Expert Comment

by:Patrick Matthews
ID: 21813248
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
Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

 

Author Comment

by:nfstrong
ID: 21813423
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
ID: 21855473
Any thoughts?
0
 

Author Comment

by:nfstrong
ID: 21901874
Anyone there?
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 21904371
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
ID: 21919148
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
 

Author Comment

by:nfstrong
ID: 21919984
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
ID: 21920002
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 2000 total points
ID: 21922611
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
ID: 21946575
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
ID: 21948967
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
ID: 21953934
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
ID: 21954425
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
ID: 21954601
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
ID: 21955280
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

777 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