Link to home
Start Free TrialLog in
Avatar of nfstrong
nfstrongFlag for United States of America

asked on

Lag analysis

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?
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

A query can work just fine, but to get more than generalities you will need to post your schema.
Avatar of nfstrong

ASKER

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?
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.
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.
Any thoughts?
Anyone there?
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
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?
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%
This calculation is taking 31PlusDelq from 5/31/2008 divided by EndingGrossAR from 5/31/2007 for the 12 mo lag.
ASKER CERTIFIED SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

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

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
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!
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):
https://www.experts-exchange.com/questions/23492961/Lag-analysis.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.