How do I get Prior weeks data

[TerriO]

I have data that falls into either individual daily dates and/or a weekly posted date (5/14/, 5/24, etc...) I need to create a formula that gets last 5 weeks of data

Week 1 Week 2 Week 3 Week 4 Week 5
$$ $$ $$ $$ $$

Also On week 5 I need to put in the header the last date?

Help








Terri

 

[synapsevampire]

Use Report->Edit selection formula->Record and place something like:

if dayofweek(currentdate) <> 1 then
{Orders.Order Date} >= Last4WeeksToSun
else
{Orders.Order Date} >= minimum(Last4WeeksToSun)-7

Select Insert->Cross-tab and place the date in the column, select Group Options->A Column Will Be Printed For Each Week, and The Value Printed for the Column Will Be The last date

-k

 

[TerriO]

I cant put it in the selection criteria because I also have to gather cumulative info as well

Terri

 

[synapsevampire]

What does cumulative data mean, for more than the 5 weeks in the above requirement? The record selection doesn't preclude you from gathering cumulative data, it simply limits the time frame.

Perhaps you can use the solution I offered in a subreport.

Please provide example data and table layout(s), report requirements (in total), and expected output

-k

 

[TerriO]

Thanks for your help!

I need

Total Costs
Last 5 weeks outlined of Costs
% Change from Previous week
Balance to budget..

Pretty simple..

Terri

 

[synapsevampire]

What does total cost mean, total for the 5 weeks, or total from the beginning of time, or?

Build a manual cross-tab using Running Totals and formulas to handle the report.

Create 2 formulas by which you base everything, as in:

@Lowest date
if dayofweek(currentdate) <> 1 then
minimum(Last4WeeksToSun)
else
minimum(Last4WeeksToSun)-7)

@Ending date
if dayofweek(currentdate) <> 1 then
maximum(Last4WeeksToSun)
else
maximum(Last4WeeksToSun)-7)

Now any formula or Running Total can base their filtering on these dates, as in:

@Ending date-7 //is the end of the previous week, etc.

-k

 

[TerriO]

Yes the total costs is from the beginning of time ..

Terri

 

[synapsevampire]

Well then you're all set.

-k

 

[TerriO]

Thanks this worked !!! I need to print the date for the weeks ending in the page header columns..

Week ending Week Ending
05/07/03 05/14/03 etc..

I tried to create a group then a rt count for the group then a if rt = 3 then groupname (date) but that didnt work.. what else can I do for 7 weeks?

Terri