Need formula to count items aged

[Lucieann]

I need help figuring out how to count items that have aged 0-30, 31-60, 61-90 and 91+ days, then total.

I have 7 columns:
Item Number
Item Description
0-30
31-60
61-90
91+
Total

I only need the item number and description to show up if there are aged records for that particular item.

Hope this makes sense....

 

[MJRBIM]

I might need more information about your data...but from you r description above, tthis formula is what you are looking for.

Name : {@Aged}
Formula : IF ({AGE_txt.0-30} > 0 OR {AGE_txt.31-60}> 0 OR {AGE_txt.61-90} > 0 OR {AGE_txt.91+} > 0) THEN "YES" ELSE "NO"


It returns data like this...


==================================================================================
Item Item 0-30 31-60 61-90 91+ Aged
Number Description
==================================================================================

1234 DOG 0 0 1 4 YES
1235 CAT 1 0 1 4 YES
1236 MOUSE 1 1 1 1 YES
1237 BIRD 0 0 0 0 NO
==================================================================================

You could also use this as a SELECTION formula to only get data that has an aged record.

Hope it helps.

 

[lbass]

I think you could do the report in a crosstab, and I'm assuming you are not sure how to group the records according to age. First create a formula {@aged}:

if datediff("d",{table.date},currentdate) in 0 to 30 then "0 to 30" else
if datediff("d",{table.date},currentdate) in 31 to 60 then "31 to 60" else
if datediff("d",{table.date},currentdate) in 61 to 90 then "61 to 90" else
if datediff("d",{table.date},currentdate) > 91 then ">91" else ""

Also create a formula {@itemdescr} which concatenates the item and description field:

totext({table.itemno},0,"&quot + " "+{table.descr}

Next insert a crosstab into the report header or footer, and use {@aged} as the column, {@itemdescr} as the row, and
count of {table.itemno} as the summary. Select the column field {@aged} and then select "Group Options" and choose "specified order". Using the dropdown list, add the formula results in the order you want, starting with "0 to 30". Then select the "Others" tab and choose "Discard all others".

That should do it.

-LB

 

[Lucieann]

The cross-tab worked great, but I can't seem to get my count right. For example, for one item I see only 4 records, but my total shows 7.

 

[MJRBIM]

You should probably do a subtotal not a count of the field.

 

[Lucieann]

Using a subtotal adds the value of my item numbers instead of giving me a count of how many records there are.

 

[dgillz]

COUNT is definately the way to go.

I would bet the there is another field besides Item_no that makes this a unique record. Can you share you database structure with us?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[Lucieann]

I have 5 different tables:

CustInfo (Acct Number, Officer Name, Cust Name, Address, Etc.)
CustNotes (Acct Number, Note Line Number, Note)
TrackedItems (Acct Number,Cat. Number, Cat. Desc, Reference, Expiration Date)
TrackedItemNotes (Acct Number, Cat. Number, Note Line Number, Note)
Collateral (Acct Number, Desc Line Number, Description

That's pretty much the data I have to use. What I'm needing to count is for any officer, the number of items in each category, separated into aged groups (0-30, 31-60, 61-90 and 90+ days) then a total for each category.

Hope this helps. I found another record that should only count for 1 item, but is instead counting 20.

 

[lbass]

So what fields are you using for the crosstab rows? If you used my suggestion, I'm guessing that you concatenated Cat.Number and Cat.Description.

Since you want the results per officer, you could group on officer and then place the crosstab in the group header or footer. If you want to show results for all officers in one crosstab in the report header or footer, then add another row within the crosstab using the officer name field, but drag it so that it is the higher order row field.

A little more info and I think we can get you there...

-LB

 

[Lucieann]

You've got it right. I have the records grouped by (1) officer, (2) customer name then (3) account number. I placed the cross-tab in the group footer (1). I concatenated Cat. Number and Cat. Description for cross-tab row, have the @aging for the columns, and a count for my total. The only problem is that the total count is not correct.

 

[lbass]

For each account number is there only one officer and one customer name? For each account number are there multiple cat.numbers and cat.descriptions?

I guess I would do some exploring by laying out the fields in the main report to see what the counts should be and then comparing to the crosstab. I would also try to recreate the groups within the crosstab by adding a customer name row and account number row--to see how this affects the numbers. When you say the "total count" is wrong, I'm assuming you mean that counts at the cell level are wrong, not that the row cells don't add to the row total or that the column cells don't add to the column total--right?

Why not show us what you are getting in the crosstab, with a sample display of the related data in the main report?

-LB

 

[Lucieann]

That is correct. For each account number, there is one officer and one customer name with possibly several cat.numbers and cat.descriptions. Here is an example of what the report should look like:

Customer: John Doe Officer: ABC
218-1 Control Agreement 8/15/2003
218-2 Control Agreement 9/15/2003
312-1 Power of Attorney 7/1/2003
316-1 Account Agreement 4/15/2003

The bold numbers are the category numbers that I need to count. What appears to be happening is that it is counting duplicate records that I have supressed. If I try a destinct count, it only counts each category one time (which in the above example would be wrong).

 

[lbass]

Suppressing records does not mean they won't get counted, as you have discovered. What is the condition you are using to suppress them? You might be able to change your record selection formula to exclude these records instead.

You might be better off creating a manual crosstab, but it will be a lot easier to do that if you are able to insert summaries instead of using running totals, and that depends on your answer to the suppression question. When you display your records after suppression of some records do you see any other duplicates that you don't want reflected in counts?

-LB

 

[Lucieann]

I wrote a formula to concatenate the category number with the subnumber. The formula (@CategorySubNumber) looks like this:

totext({TITM1301___Test_3_No_Dup.Category},0) + "-" +
totext({TITM1301___Test_3_No_Dup.SubNumber},0)

I am using the Supress If Duplicated option.