Variance between weeks

[pgh2377]

I need to show the variance in a report between two different week periods. For example I have the following in a table:

Week Sales Qty
11/28 $50 4
12/5 $35 3

I need a report that would show that, plus the variance:
Week Sales Qty
11/28 $50 4
12/5 $35 3
Change (15) (1)

Is this possible? Do I need multiple tables or queries?

 

[GingerR]

in the report footer, you could use dlookup function to get the Sales and Qty from the prior week and subtract it from the Sales and Qty from the current week.

How are you getting the dates for the report? Is that the only data in the table, or are you prompting the user, or are you getting the date from a form before running the report?

an example would be this (Table name = "TableName", week field = "WeekDate"):

=DLookUp("Amount","TableName","WeekDate = #12/5/04#")-DLookUp("Amount","TableName","WeekDate = #11/28/04#")

You can do the same for QTY. this would be hardcoding in the dates. If you want to pursue this, let us know where you dates are (i.e. from a form?) and if you need help re-writing this to accomodate changing dates from week to week, let us know.

 

[pgh2377]

I have a qry the does a make table. I enter in the date range (which is usually 2 weeks) and the table gives me the following:

Week of Strenth sales
11/28/04 25 890
11/28/04 50 1090
12/5/04 25 900
12/5/04 50 1200

 

[GingerR]

when you say "usually 2 weeks": when is it NOT 2 weeks? then what do you want the forumla to be?

for two weeks: use dmax to find the current week in the table, dmin to find the min date (prior week), then dsum to sum up stength:

dsum("Strength","TableName","[Week Of] = #" & dmax("[Week Of]","TableName") & "#") - dsum("Strength","TableName","[Week Of] = #" & dmin("[Week Of]","TableName") & "#")

This is saying: sum up the Strengths from my table where the week is the max week listed, then subtract from that the sum of the Strengths from my table where the week is the min week listed.

sub in your Table name for TableName.

then do the same for "Sales", substituting "Sales" for "Strength". Put these formulas in text boxes in a report footer or even in a query if you want.

g

 

[pgh2377]

Getting an error message, I need to show the difference between the stregths between the two weeks. For example, the 25 increased $10.

 

[GingerR]

1) Have you tried what I sent you? How did it work? I set up my test db exactly as you described and it worked exactly as you described you wanted it to.

2) what have you tried? what is the error message?

3) I do not understand your last post. Are you just reiterating your previous issue?

 

[pgh2377]

I apologize for not being more in depth, your help has been great so far. Well, when I run it I get a zero. I thought I would start easy, so I have to the following:

TABLE: A1

DATE STRENGTH RXS
11/28/04 25 25
12/5/04 25 30

=DSum("RXS","A1","[DATE]= #" & DMax("[DATE]","A1") & "#")-DSum("RXS","A1","[DATE]= #" & DMin("[DATE]","A1") & "#")

 

[GingerR]

hmmmmm....well it appears ok. Is RXS data type a number (vs. Text)? Might sound like a dumb question but I have to ask. Is "DATE" a date data-type?

to troubleshoot, make a text box that just says

=DMax("[DATE]","A1")

and other: =DMin("[DATE]","A1")

then one where you replace the whole dmax thing with an actual date (12/5/04) and the whole dmin thing with the other date (11/28/04). see how those work.

 

[pgh2377]

Well, I just started with =DMax("[DATE]","A1" and that worked great. Now if I can only get the rest...I trield the DMax first but got an error.