move data into certain column based on dates 1

[Hueby]

Hi all,

Crystal Reports 10... MS SQL DB,

I have in a detail line these fields:

Date1 and Money .... then to the right a couple of date columns. These columns are generated by taking the CURRENT date, then adding a week to 1st column, 2 weeks to the 2nd, and so on. It looks like this:

Date1 Money 03/15/07 03/22/07 03/29/07
03/16/07 $150 - X X X
03/25/07 $500

Now, where the X's are I am working on a formula to take the dollar amount (Money) and place it under one of the three Date columns based on Date1. This is being done to "forecast" money.

Obviously, the three date columns will change each new day the report is ran.... I need some help writing a formula to look at Date1 and put it under the correct week on the right. Make sense?

 

[Kendel]

Let call the 3 dates on the right: Wk1, Wk2, and Wk3

Place a formula cal D1_Wk1 under Wk1.
if (date1 >= Wk1 and date1<Wk2 and date1<Wk3) then
date1
else
""

Create similar formula D1_Wk2 and D1_Wk3

Give it a try and see how it work. You will need to play around with it.

 

[Hueby]

thanks, I have something working now!

 

[Madawc]

A crosstab might do the same job with less work.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10