Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Aug 2003
    Location
    Virginia
    Posts
    15

    Unanswered: Possible to calculate percent change between periods?

    I am wondering if it is possible to generate a calculated field that shows the percent change from one period to another for a particular field.

    Specifically, I want to show the percent change for each month from the similar month one year ago and have the results match up to the corresponding month. So the percent change from June 2002 to June 2003 would be in a table with the date as June 2003. I am now exporting the data into Excel and performing the calculations there and importing the results back into Access. I would like to do the whole thing within Access if possible.

  2. #2
    Join Date
    Aug 2003
    Location
    Virginia
    Posts
    15
    I have come up with one solution where I create a previous year date field that is based on a function of the current year date. Then run a make table query that generates a second table with the two date fields in it as well as all of the data. Then I run a query that calculates the percent change between the comparable fields in both the new table and the origional tables using the new date field as a join to the original date field.

    It seems to work but I wonder if there is a cleaner way to do this. I have well over 100 fields in this table and it is a lot of calculated fields to generate (I also need to do this with five additional datasets all with different field names so that works out to about 600 calculated fields). I could probably create the SQL string using the concatenate function in Excel but I am convinced that there has to be an easier way to do this.

  3. #3
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    See if this works for you:

    Make a query called "PriorYearData" as follows:

    Select all data in the table, EXCEPT the year. Instead of the year, select YEAR+1 as an alternate field name. Save the query.

    Make another query using your original table, and the newly created "PriorYearData" query. Join them by year and month.

    Data from your original table will now be matched to data from the prior year. You will be able to easily calculate the percent change.

  4. #4
    Join Date
    Aug 2003
    Location
    Virginia
    Posts
    15
    Thanks for your suggestion. Unfortunately it still leaves me with the task of creating over 600 calculated fields.

    I figured out a temporary solution. I ran a crosstab on the table using the year and data descriptions( current column headers) as row headers and the month as the column headers. This gives me a table where january data is all in one column, february data is all in one column, etc. Then I created a query that generates a new year field that is equal to the current minus one. I then do a calculation for each month matching the old date to the new date and the data descriptions to get the year to year percent change.

    Next I run a series of consecutive append queries to put all the data in one column, all the month names in another, another for the years, and one for the data descriptions. I then recreate the date field and then run a crosstab based on that table.

    It works well and I only hade to create twelve calculated fields. I am sure there must be some way to do this by running a loop in VBA which would probably be a lot cleaner and use less resources.

  5. #5
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    It seems to me it would be easier to generate a spreadsheet to display your results, rather than using a cross-tab query based on an intermediate table.

    Would you be interested in seeing something that could take your table and produce such a spreadsheet (months across the top, fields listed down the first column, populated with the %change from the prior year) using no intermediate tables?

    The only requirement to use it would be: all boxes that run the app have to have excel installed.

  6. #6
    Join Date
    Aug 2003
    Location
    Virginia
    Posts
    15
    If it is going to be more efficient than the way I have set it up, sure. What I have done in the past is set up an Excel workbook with a page of just references to a workbook that is created using the "output to" feature in access and perform the calculations in that workbook. The problem is that the data needs to be in Access for a third party app that I need to ultimately get the data into. I can always link it back into Access from Excel if need be.

    I appreciate your insight - any suggestions would be welcome.

  7. #7
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Is there a unique key on year and month in your table, or do you have multiple records for each year and month?

  8. #8
    Join Date
    Aug 2003
    Location
    Virginia
    Posts
    15
    The table is originally SAS output where each row has 14 fields. Code, Year, and the twelve months. Each of the twelve months contains data. It has 20 years worth of data for each code so the year is repeated once for each unique code. There are up to 150 different codes. -- So in this case the row header is actually two fields - code and year and the column headers are the twelve months. --

    After some manipulation I have a table with the dates (in regular date format) as the row headers and the codes as the column headers.

    So I basically have two possible table formats. I am not sure if that answers your question or not. I think the second format I described would fit what you are asking.

    The original data format seems pretty conducive to calculating a year over year percent change and I only have to create a calculation for each month instead of each code (series). Then I just run it through the same manipulation process that I use for the level data (I can even use the same queries - just use a temporary table and rename once I am done).

  9. #9
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    What do you want the results to look like? Similar to the input: Code, Year, 12 months, EXCEPT the months will contain %change from prior year? Or do you prefer a different format?

  10. #10
    Join Date
    Aug 2003
    Location
    Virginia
    Posts
    15
    Ultimately the data needs to have one column for each code (series) with the dates as row headers (it's monthly data).

  11. #11
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Are the month field names characters? Like "Jan", "Feb", etc?

  12. #12
    Join Date
    Aug 2003
    Location
    Virginia
    Posts
    15
    Yes

  13. #13
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    What's the name of the table where your SAS input is stored?

  14. #14
    Join Date
    Aug 2003
    Location
    Virginia
    Posts
    15
    IP_SA.txt (tab delimited)

  15. #15
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    If you can import it into a table called SASInput (or link it as a table), and create these three queries, I think you'll have what you need:

    GIVEN
    table: SASInput, with fields: Code, Year, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec

    Create these queries:

    PriorYearSASInput

    SELECT SASInput.Code, [SASInput].[Year]+1 AS PriorYear, SASInput.Jan, SASInput.Feb, SASInput.Mar, SASInput.Apr, SASInput.May, SASInput.Jun, SASInput.Jul, SASInput.Aug, SASInput.Sep, SASInput.Oct, SASInput.Nov, SASInput.Dec
    FROM SASInput;

    PercentChange

    SELECT SASInput.Code, SASInput.Year & "/01" As Period, IIf(Nz([PriorYearSASInput].[Jan], 0) = 0, Null, Format$(([SASInput].[Jan] - [PriorYearSASInput].[Jan]) / [PriorYearSASInput].[Jan],"Percent")) AS Change
    FROM SASInput INNER JOIN PriorYearSASInput ON (SASInput.Code = PriorYearSASInput.Code) AND (SASInput.Year = PriorYearSASInput.PriorYear);
    UNION SELECT SASInput.Code, SASInput.Year & "/02" As Period, IIf(Nz([PriorYearSASInput].[Feb], 0) = 0, Null, Format$(([SASInput].[Feb] - [PriorYearSASInput].[Feb]) / [PriorYearSASInput].[Feb], "Percent")) AS Change
    FROM SASInput INNER JOIN PriorYearSASInput ON (SASInput.Code = PriorYearSASInput.Code) AND (SASInput.Year = PriorYearSASInput.PriorYear);
    UNION SELECT SASInput.Code, SASInput.Year & "/03" As Period, IIf(Nz([PriorYearSASInput].[Mar], 0) = 0, Null, Format$(([SASInput].[Mar] - [PriorYearSASInput].[Mar]) / [PriorYearSASInput].[Mar], "Percent")) AS Change
    FROM SASInput INNER JOIN PriorYearSASInput ON (SASInput.Code = PriorYearSASInput.Code) AND (SASInput.Year = PriorYearSASInput.PriorYear);
    UNION SELECT SASInput.Code, SASInput.Year & "/04" As Period, IIf(Nz([PriorYearSASInput].[Apr], 0) = 0, Null, Format$(([SASInput].[Apr] - [PriorYearSASInput].[Apr]) / [PriorYearSASInput].[Apr], "Percent")) AS Change
    FROM SASInput INNER JOIN PriorYearSASInput ON (SASInput.Code = PriorYearSASInput.Code) AND (SASInput.Year = PriorYearSASInput.PriorYear);
    UNION SELECT SASInput.Code, SASInput.Year & "/05" As Period, IIf(Nz([PriorYearSASInput].[May], 0) = 0, Null, Format$(([SASInput].[May] - [PriorYearSASInput].[May]) / [PriorYearSASInput].[May], "Percent")) AS Change
    FROM SASInput INNER JOIN PriorYearSASInput ON (SASInput.Code = PriorYearSASInput.Code) AND (SASInput.Year = PriorYearSASInput.PriorYear);
    UNION SELECT SASInput.Code, SASInput.Year & "/06" As Period, IIf(Nz([PriorYearSASInput].[Jun], 0) = 0, Null, Format$(([SASInput].[Jun] - [PriorYearSASInput].[Jun]) / [PriorYearSASInput].[Jun], "Percent")) AS Change
    FROM SASInput INNER JOIN PriorYearSASInput ON (SASInput.Code = PriorYearSASInput.Code) AND (SASInput.Year = PriorYearSASInput.PriorYear);
    UNION SELECT SASInput.Code, SASInput.Year & "/07" As Period, IIf(Nz([PriorYearSASInput].[Jul], 0) = 0, Null, Format$(([SASInput].[Jul] - [PriorYearSASInput].[Jul]) / [PriorYearSASInput].[Jul], "Percent")) AS Change
    FROM SASInput INNER JOIN PriorYearSASInput ON (SASInput.Code = PriorYearSASInput.Code) AND (SASInput.Year = PriorYearSASInput.PriorYear);
    UNION SELECT SASInput.Code, SASInput.Year & "/08" As Period, IIf(Nz([PriorYearSASInput].[Aug], 0) = 0, Null, Format$(([SASInput].[Aug] - [PriorYearSASInput].[Aug]) / [PriorYearSASInput].[Aug], "Percent")) AS Change
    FROM SASInput INNER JOIN PriorYearSASInput ON (SASInput.Code = PriorYearSASInput.Code) AND (SASInput.Year = PriorYearSASInput.PriorYear);
    UNION SELECT SASInput.Code, SASInput.Year & "/09" As Period, IIf(Nz([PriorYearSASInput].[Sep], 0) = 0, Null, Format$(([SASInput].[Sep] - [PriorYearSASInput].[Sep]) / [PriorYearSASInput].[Sep], "Percent")) AS Change
    FROM SASInput INNER JOIN PriorYearSASInput ON (SASInput.Code = PriorYearSASInput.Code) AND (SASInput.Year = PriorYearSASInput.PriorYear);
    UNION SELECT SASInput.Code, SASInput.Year & "/10" As Period, IIf(Nz([PriorYearSASInput].[Oct], 0) = 0, Null, Format$(([SASInput].[Oct] - [PriorYearSASInput].[Oct]) / [PriorYearSASInput].[Oct], "Percent")) AS Change
    FROM SASInput INNER JOIN PriorYearSASInput ON (SASInput.Code = PriorYearSASInput.Code) AND (SASInput.Year = PriorYearSASInput.PriorYear);
    UNION SELECT SASInput.Code, SASInput.Year & "/11" As Period, IIf(Nz([PriorYearSASInput].[Nov], 0) = 0, Null, Format$(([SASInput].[Nov] - [PriorYearSASInput].[Nov]) / [PriorYearSASInput].[Nov], "Percent")) AS Change
    FROM SASInput INNER JOIN PriorYearSASInput ON (SASInput.Code = PriorYearSASInput.Code) AND (SASInput.Year = PriorYearSASInput.PriorYear);
    UNION SELECT SASInput.Code, SASInput.Year & "/12" As Period, IIf(Nz([PriorYearSASInput].[Dec], 0) = 0, Null, Format$(([SASInput].[Dec] - [PriorYearSASInput].[Dec]) / [PriorYearSASInput].[Dec], "Percent")) AS Change
    FROM SASInput INNER JOIN PriorYearSASInput ON (SASInput.Code = PriorYearSASInput.Code) AND (SASInput.Year = PriorYearSASInput.PriorYear);

    Results (you can name this one anything you like)

    TRANSFORM First(PercentChange.Change) AS FirstOfChange
    SELECT PercentChange.Period
    FROM PercentChange
    GROUP BY PercentChange.Period
    PIVOT PercentChange.Code;

    After you import or link the data and create the queries, run the query called Results (or whatever you named it).

    I'm not sure how many colums a cross-tab query can have, though if I had to guess, I'd guess 256. You might exceed the max with your data.

    Let me know if how it goes.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •