Moving Sum Omitting Low Values

[Cornbeef]

I currently have a table of weekly measurements for different categories. I
need to get the sum of the previous 6 weeks for some calculations, but there
are some low values that I want to skip. The criteria for skipping a record
is the TSqFootage being less than 10% of the category standard, which is
35000 for A. I have included a sample of the data below. Each of the sums
should include six weeks worth of data. For example to get the first sum,
the records for 6/30, 7/14, 7/28, 8/4, 8/11, and 8/18 were used. The
standards are located in a different table.

Category (PK) Week (PK) TSqFootage Sum
A 18-Aug-07 32779.25 238492.8
A 11-Aug-07 42786.1 241209.15
A 04-Aug-07 47194.35
A 28-Jul-07 43792.7
A 21-Jul-07 0
A 14-Jul-07 27787.2
A 07-Jul-07 179.55
A 30-Jun-07 44153.2
A 23-Jun-07 35495.6


I would appreciate any help I can get on this. Thanks

 

[James A. Fortune]

I currently have a table of weekly measurements for different categories. I
need to get the sum of the previous 6 weeks for some calculations, but there
are some low values that I want to skip. The criteria for skipping a record
is the TSqFootage being less than 10% of the category standard, which is
35000 for A. I have included a sample of the data below. Each of the sums
should include six weeks worth of data. For example to get the first sum,
the records for 6/30, 7/14, 7/28, 8/4, 8/11, and 8/18 were used. The
standards are located in a different table.

Category (PK) Week (PK) TSqFootage Sum
A 18-Aug-07 32779.25 238492.8
A 11-Aug-07 42786.1 241209.15
A 04-Aug-07 47194.35
A 28-Jul-07 43792.7
A 21-Jul-07 0
A 14-Jul-07 27787.2
A 07-Jul-07 179.55
A 30-Jun-07 44153.2
A 23-Jun-07 35495.6


I would appreciate any help I can get on this. Thanks

tblSqFootage
SFID AutoNumber
Category Text
Week Date/Time dd-mmm-yy
TSqFootage Double
SFID Category Week TSqFootage

1 A 18-Aug-07 32779.25
2 A 11-Aug-07 42786.1
3 A 04-Aug-07 47194.35
4 A 28-Jul-07 43792.7
5 A 21-Jul-07 0
6 A 14-Jul-07 27787.2
7 A 07-Jul-07 179.55
8 A 30-Jun-07 44153.2
9 A 23-Jun-07 35495.6

tblCategories
CID AutoNumber
Category Text
CategoryStandard Double
CID Category CategoryStandard
1 A 35000

Note: CID should be used as a foreign key in tblSqFootage unless the
exact text of the Category at the time of the weekly measurement is
required for historical purposes.

qrySqFootageSum:
SELECT Category, Week, TSqFootage, (SELECT SUM(A.TSqFootage) FROM
tblSqFootage AS A WHERE A.Category = tblSqFootage.Category AND
A.Category & A.Week IN (SELECT TOP 6 B.Category & B.Week FROM
tblSqFootage AS B WHERE B.Week <= tblSqFootage.Week AND B.TSqFootage >
..1 * (SELECT C.CategoryStandard FROM tblCategories AS C WHERE C.Category
= tblSqFootage.Category))) AS theSum FROM tblSqFootage;

!qrySqFootageSum:
Category Week TSqFootage theSum
A 18-Aug-07 32779.25 238492.8
A 11-Aug-07 42786.1 241209.15
A 04-Aug-07 47194.35 198423.05
A 28-Jul-07 43792.7 151228.7
A 21-Jul-07 0 107436
A 14-Jul-07 27787.2 107436
A 07-Jul-07 179.55 79648.8
A 30-Jun-07 44153.2 79648.8
A 23-Jun-07 35495.6 35495.6

I didn't test the multi-category case, but it looks like it should work
for that as well. Make sure you have tblCategories and that you change
any field or table names I used to match yours.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

I currently have a table of weekly measurements for different categories. I
need to get the sum of the previous 6 weeks for some calculations, but there
are some low values that I want to skip. The criteria for skipping a record
is the TSqFootage being less than 10% of the category standard, which is
35000 for A. I have included a sample of the data below. Each of the sums
should include six weeks worth of data. For example to get the first sum,
the records for 6/30, 7/14, 7/28, 8/4, 8/11, and 8/18 were used. The
standards are located in a different table.

Category (PK) Week (PK) TSqFootage Sum
A 18-Aug-07 32779.25 238492.8
A 11-Aug-07 42786.1 241209.15
A 04-Aug-07 47194.35
A 28-Jul-07 43792.7
A 21-Jul-07 0
A 14-Jul-07 27787.2
A 07-Jul-07 179.55
A 30-Jun-07 44153.2
A 23-Jun-07 35495.6


I would appreciate any help I can get on this. Thanks

One more thing. Perhaps add an ORDER BY so that the TOP doesn't get
confused if the weeks aren't descending as in your example.

SELECT Category, Week, TSqFootage, (SELECT SUM(A.TSqFootage) FROM
tblSqFootage AS A WHERE A.Category = tblSqFootage.Category AND
A.Category & A.Week IN (SELECT TOP 6 B.Category & B.Week FROM
tblSqFootage AS B WHERE B.Week <= tblSqFootage.Week AND B.TSqFootage >
..1 * (SELECT C.CategoryStandard FROM tblCategories AS C WHERE C.Category
= tblSqFootage.Category) ORDER BY B.Week DESC)) AS theSum FROM tblSqFootage;

James A. Fortune
(e-mail address removed)

 

[Cornbeef]

I had to remove a decimal place from the ..1 but it worked fine after that
with the previous code. However it stopped summing when I added the order by
into it. The table should always be ordered so there shouldn't be a problem
though.

I really appreciate the help.

 

[Cornbeef]

Sorry for the double post, but it seems that it is not fully working on
categories beside A. I can send a copy of the database, reduced to about
320kb, if that would help. I could also post the tblCategories and
tblSqFootage data. There are 12 categories and 213 sqfootage records. Just
let me know which would be easier.

Once again thank you for the help.

 

[James A. Fortune]

I had to remove a decimal place from the ..1 but it worked fine after that
with the previous code. However it stopped summing when I added the order by
into it. The table should always be ordered so there shouldn't be a problem
though.

I really appreciate the help.

I don't know how the extra period got to your newsreader. I actually
ran the query after adding in the ORDER BY part. It worked for me.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

Sorry for the double post, but it seems that it is not fully working on
categories beside A. I can send a copy of the database, reduced to about
320kb, if that would help. I could also post the tblCategories and
tblSqFootage data. There are 12 categories and 213 sqfootage records. Just
let me know which would be easier.

tblCategories should have a record/row for each category. You can get a
complete list of them by running a query such as:

SELECT DISTINCT Category FROM tblSqFootage WHERE Category IS NOT NULL;

Just type in the 12 values along with the corresponding CategoryStandard
into tblCategories. I tried adding another category to tblCategories
(Category B, CategoryStandard 22000) and added B 28-Aug-07 333333 to
tblSqFootage. It showed up in qrySqFootage.

James A. Fortune
(e-mail address removed)

 

[Cornbeef]

My tblCategories is setup as you said, but I am still having issues.

All my tblSqFootage data shows up in the query, but theSum field is not
filling in correctly. It works fine for category A, but on the next
category, B, only the last six records have a non-null value. Some of the
categories have only one or two non-null values.

 

[James A. Fortune]

My tblCategories is setup as you said, but I am still having issues.

All my tblSqFootage data shows up in the query, but theSum field is not
filling in correctly. It works fine for category A, but on the next
category, B, only the last six records have a non-null value. Some of the
categories have only one or two non-null values.

Looking back at the query I see a reason for what you are experiencing.
I just needed to know that there was a problem for multiple
categories. I'll expand my test data and that should allow me to be
able to find and fix the problem without having to see your data.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

My tblCategories is setup as you said, but I am still having issues.

All my tblSqFootage data shows up in the query, but theSum field is not
filling in correctly. It works fine for category A, but on the next
category, B, only the last six records have a non-null value. Some of the
categories have only one or two non-null values.

Try this:

SELECT Category, Week, TSqFootage, (SELECT SUM(A.TSqFootage) FROM
tblSqFootage AS A WHERE A.Category = tblSqFootage.Category AND
A.Category & A.Week IN (SELECT TOP 6 B.Category & B.Week FROM
tblSqFootage AS B WHERE B.Week <= tblSqFootage.Week AND B.Category =
tblSqFootage.Category AND B.TSqFootage > .1 * (SELECT C.CategoryStandard
FROM tblCategories AS C WHERE C.Category = tblSqFootage.Category) ORDER
BY B.Week DESC)) AS theSum FROM tblSqFootage;

It limits the Category to the current one when selecting the top six.

James A. Fortune
(e-mail address removed)

 

[Cornbeef]

I checked the numbers based on the new code and it seems to work for all
categories. The only thing I noticed is there is 213 records in the table
and sometimes only 212 show in the query. The sum is correct like the
missing value is there, but it is just not shown. I can reopen the query and
it will show 213 records.

In any case thank you very much for your help.

 

[James A. Fortune]

I checked the numbers based on the new code and it seems to work for all
categories. The only thing I noticed is there is 213 records in the table
and sometimes only 212 show in the query. The sum is correct like the
missing value is there, but it is just not shown. I can reopen the query and
it will show 213 records.

You stumped me there. There's no WHERE. Maybe delete and recreate the
primary key.

In any case thank you very much for your help.

I appreciate the thanks.

James A. Fortune
(e-mail address removed)