Count for Date Ranges???

[TudorSmith]

I have a report based on Stock Usage. I want to capture the count if stock items picked when the pick date falls between two dates.

I have this formula:

IF CDate({DatePicked}) In #01/01/2001# to #31/01/2001# then
    Count ({PARTNO_INV}, {PARTNO_INV})
ELSE
   0

My complete period range is 01/01/2001 to 31/12/2001, so I am attempting 12 columns (one per month). Trouble is, I get the grand total in each column (Doh!!!)

Is there something wrong with my formula?

birklea

 

[dgillz]

If you want usage, you don't want the count, you want a transaction qty.

IF CDate({DatePicked}) In #01/01/2001# to #31/01/2001# then
{TransQty} else 0

Then just sum these 12 columns.

You shuld also look at a cross tab report. When you make a date field a column of a crosstab, it will automatically prompt you to determine how often you want the data summarized by date (in your case monthly). Cross tab reports will generate as many columns as are needed. If you ask for 24 months when you answer the parameter, you would get 24 columns, if oyu ask for 12, you will get 12 columns, etc. All this is done with ONE formula - a record selection formula tat restricts the report to the data range parameter.

let me know if you have any questions.


Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[TudorSmith]

HI,

I am not sure I understand the {TransQty} field bit! Am I to create such a field or is this a global field I can use? (Bit of a novice!!!)

I'll also play about with the CrossTab Report see what transpires!

 

[dgillz]

{TransQty} or something very much like that should already exist in your database, just as {DatePicked} already does. If you do a count you will receive a value of 1 for every record, when the transaction qty could be hundreds. thats why you need a sum

Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office