Solved

Lag analysis

Posted on 2008-06-17
18
1,255 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
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 92

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
 

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 92

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

920 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

18 Experts available now in Live!

Get 1:1 Help Now