converting date to week 1

[ajr1705]

I need to summerise data by week for a date range. Is there a specfic function I can use to convert the date to a week number


var cr 8.5

 

[LLowrance]

DatePart ("ww", {FieldName})

This will give you the week number.

 

[synapsevampire]

Depends on what you mean by summarize, that's not exactly a descriptive statement.

You can insert a group by the date, and then change the options to by WEEK. Then palce any field in th edetails, right clikc it and select insert summary->and choose the sort of aggregate you need, just make sure that you select by that week.

You can also use a crosstab, and when specifying the date as a row or column change it to by week.

If neither of these suit the requirements, then post the actual requirements by stating your database, example data and expected output.

-k

 

[ajr1705]

I need to write a report to show only new customers. I need to run the report so that it runs for 2 periods and check who new customers are in the second period.

The problem I am facing is that the customers are some time duplicated and I need to exclude this.

I need to check by Name and postcode to see if they are duplicated.

E.g.

Table

Cust Nam C Date Amount

1 a qq Jan06 20
2 b ww Jan05 20
3 c rr Jan06 20
4 b ww Jan 07 20
5 d rr Jan07 20
1 a qq Jan07 20

I am looking for the result to be customer 5 in Jan 07

Any suggestion would be appreciated


V Cr 8.5
DB Oracle

 

[lbass]

I'm not following your example, but you could determine who was new by creating a SQL expression like {%mindate}:

(select min(A.`date`) from Table A where
A.`customer` = Table.`customer`)

Substitute your actual field and table names above, but leave "A" as is, since it represents an alias table name.

Then you could create a formula like this:

if {%mindate} in <period 2> then
"New" else "Continuing"

This assumes that your concern with duplication was really about how to classify someone as new, and I may be wrong in my assumption.

-LB

 

[ajr1705]

Thnaks seems a lil comlex

 

[lbass]

Sometimes that's all that works. It would help if you clarified what you are looking for. If you tried my suggestion and had trouble with it, it would be helpful if you described the issue you ran into.

-LB