Hello John,
As usual thank you for clear and fast answer.
It take always some time study your mail but at the end there is always the solution.
Correct, the third row is 201503-15% and not what I wrote and there is a customer ID
I was hoping in existing function as SUM or similar but there is not and effectively I can follow your suggestion applying the formula of slope using VBA.
Regarding your offer, what can I say, if you have some professional "suggestion" of code is welcome but in any case your mail it is enough for me to go on
Thank you
Valentino
Da: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Inviato: lunedì 10 agosto 2015 15.12
A: MS_Access_Professionals@yahoogroups.com
Oggetto: Re: [MS_AccessPros] Slope
Valentino-
First, I assume, your third row should be:
Customer1 201503 15%
Slope over a sequence of numbers is defined as:
If it is the case that you have an open credit reading each month, then x minus previous x (number or months) will always be 1. Thus your slope calculation becomes the sum of the percentage intervals divided by the count of intervals. (Sum of 1 squared will be the Count.)
If data is missing for any month, then the calculation becomes more cumbersome because some x intervals might then be more than the value 1 month.
It might be possible to write a custom VBA function to do the calculation. You would pass it the name of the table, the name of the column providing the key value (CustomerID ?), and the name of the x and y columns. The code would have to open a recordset on the table filtered for the key value and sorted ascending by x values. It could then run through the available rows, performing the difference, multiply, and square calculations, then return a final value. The trick is knowing the "unit" of the x column - in your case one month, but the value in the column is year and month number. The calculation is easy when the x values are all in the same year, but become problematic when you cross a year boundary because, for example, 201501 minus 201412 is 89, not 1. You would have to write code that's specific to the custom format of the x values presented by your problem.
Do you need me to take a stab at writing the code?
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Aug 10, 2015, at 2:24 PM, valentino.avvisati@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Hello,
sombody knows how to do slope function in Access?
I have 3 fields:
Customer
X = month-year
Y = percentage of open credit> of 1 year respect total open credit
Customer1 201501 10%
Cusotmer1 201502 20%
Cusotmer1 201502 15%
Customer2 201501 15%
Customer2 201502 10%
If I analyse the Graph in Excel grouping all customer togheter I can see, using regression line, if the situation of open credit is gettings worst referred to the age of the credit (more or less than one year).
the next step is identify the customer that are increasing this percentage
IN other words ar eall customer for which the slope is positive
I would like to have a sort of grouping query where for each customer I can have the slope in order to have the list ordered for slope value
that at the end is the indication who are the customers where the open credit is getting worst in the period selected
any idea?
Posted by: "Valentino " <valentino.avvisati@gmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (3) |
Tidak ada komentar:
Posting Komentar