Rolling 12 month growth Percentage **Theory Needed**

[hockeyman9474]

Have 2015, 2016, and current Sales information for this year stored in a table called Archive. Fields are StoreName, Transaction Sales Month (i.e. 1 for January, 2 for February...)Sales Count, Sales Year. I need to create a rolling 12 month report. So for example if we were through March:

I would need to compare Jan-Mar of this year to the same months last year and show growth percentage for each month for each store
Since April-December of this year hasn't occurred, I would need to compare those months in 2016 to 2015.

Unless there is an easy fix here that I am missing, I have been struggling with theory using a series of queries and tables, but I haven't found a solution.

Current Working Theory Assuming we're through March:
Query Archive Table for Jan-March this year and last year
Query Archive Table for April-Dec last year and 2015
Calculate GP for both and add all to one table, crosstab 12 months, and use as datasource for a report

I am OK with creating queries, it's the theory behind filtering through a set of queries is what's eating me.

[ranman256]

I had a form with the start date and end date boxes.
filling in the end date, automatically filled in the start date 12 months back.
i had a tReport table. This held all the results to report on.
a macro then ran several queries, to fill up the tReport table.
a report or crosstab was then ran against this table.

[aytee111]

Start with the simplest part of this - one value to be used in the comparison per year/month. This could be in a query(ies) or a temporary table that is created:
3 fields: Year plus Month plus value to compare

Now you will have multiple records, from this month going back into the past.

Create a query taking the top 2 of these records sorted descending by year (so if you have 2017 the top 2 will be 2017 and 2016 by month, otherwise it will be 2016 and 2015, etc).

Base your report off this query, add a grouping by month, don't print anything in the detail section, in the month footer section have the comparison values and the years that they apply to.

[hockeyman9474]

ayetee111

I'll start with, I solved this, but this a series of queries, but more queries than I had hoped. I like to keep it simple. I simply overthought it and when I came to my senses I added Month as an additional row to a crosstab query. I didn't realize I could do that, but I could.

With that said, I am all about simplification, and I follow you logic theoretically, but I ma having trouble converting it into queries. Let me give you a bit more.

I have 65 different stores, stored in the table by store number (not a primary key). If I am using 3 years of information, I could have up to 3 records per store assuming they have been around all 3 years (Jan 17, Jan 16, and Jan 15). I run a query that looks for records <= current month and current year, which finds all records. Now I am looking to create a query that finds the top 2 records for each store. Let's assume all current stores have been around since last January. I would need a query to find the top 2 records for each store for each month. So for January, I will find Jan 2017 and Jan 2016, but February hasn't completed yet so the top two records for February for each store will be from 16 and 15. I need to crosstab and have the report show the stores by mistrict, not by month. Is your theory still valid?

[aytee111]

If I am using 3 years of information, I could have up to 3 records per store

In the first query - store, year, month, value - you will have up to 36 records per store, not 3.

Second query - sort by store, month, year and take top 2 records (so, 01/17, 01/16 - or 06/16, 06/15).

Now you have the data that you want. If there are missing fields then add them to query 1. NOW create the report from query 2, forget about transform and crosstab and all that, let the report handle all of that for you.

[hockeyman9474]

I follow but I am not sure I have ever created a query like that before. I know how to select the top x from the entire query but no the top 2 from each store. I assume it needs to e accomplished in SQL view. i.e. SELECT Top 2 MONTHS, YEARS, STORES
FROM tablename
ORDER BY YEARS ASC

[aytee111]

This explains how to create the query you are looking for: http://www.allenbrowne.com/subquery-01.html#TopN

Give it a try and come back if you are having trouble with it.

[hockeyman9474]

OK still struggling, but let em show you the code I am using:
Information is being pulled from a table called Archive. Using the Top N example, I am writing code in SQL design. A table that queries unfiltered data from Archive with a nested table with the alias Archive 2 that queries Archive for the top 2 records for each dealer. But I am looking for Top 2 months and years (separate columns). so with the TOP command can I specify 2 fields here. Transaction Sales Month is a number between 1 and 12. So perhaps I need the first table to query for top 2 years for every dealer and the second for top 2 months? Is that logic better than below?

SELECT Archive.Dealer, Archive.[Transaction Sales Year], Archive.[Transaction Sales Month], Archive.[Sales Count]
FROM Archive
WHERE Archive.Dealer IN
(
SELECT TOP 2 [Transaction Sales Year], [Transaction Sales Month]
FROM Archive as Archive2
WERE Archive2.Dealer=Archive.Dealer
);

[aytee111]

Because your year and month are two separate fields, that complicates them. Create a pre-query and make them one field:

Query 1:
SELECT StoreName, [SalesMonth] & [SalesYear] AS SalesMY, SalesYear, SalesMonth FROM Archive;

Query 2: (add more fields if desired)
SELECT StoreName, SalesYear, SalesMonth FROM query1 WHERE query1.SalesMY IN
(SELECT TOP 2 SalesMY
FROM query1 AS Dupe
WHERE Dupe.StoreName = query1.StoreName
ORDER BY SalesMY DESC);

[hockeyman9474]

I was thinking that actually a while back and it worked like a charm. I am going to add some fields now such as breaking apart the Dealer field and extracting Region, District, and Dealer Code and then get the info into a report. You said that the formatting I have been doing such as cross-tabbing etc. can all be in done in the report right?

[aytee111]

Most formatting can be done in reports. However, it is better to do what you can in the query, such as calculated fields or joining in other tables/queries. Having said that, I have no idea what "cross-tabbing" is!