Calculating Average 2

[CJP12711]

It seems like this should be easier than it is turning out to be, but I can't get this to work!

The Case table has the case ID number.
The Inventory table has the Inventory ID and item #.

There can be several different item #s for each case ID, and even several of the same item # in each case. I've restricted the report to show only the item numbers I am interested in, with the field chooser.

I need to find out the average number of inventory item # 123 for each case.

I can sum the # of inventory items used in the case, and I can get the number of distinct cases, but for some reason when I try doing an average, it doesn't work.

Case:
ID 1
ID 2
ID 3

Inventory:
Case ID 1, Inventory Item #5
Case ID 1, Inventory Item #5
Case ID 1, Inventory Item #4
Case ID 2, Inventory Item #11
Case ID 3, Inventory Item #7
Case ID 3, Inventory Item #8

I can get it to show that I have 3 cases here, and 6 items used, but I need to show that there is an average of 2 items per case.

Any help is greatly appreciated!

 

[SmpTraining]

Create a formula to divide your two subtotals.

When in the formula editor, subtotal and grand totals are available to select as part of your formula. So it will look something like:

Report total
Count({ItemID}) / DistinctCount({CaseID})

SubTotal
Count({ItemID},{GroupField}) / DistinctCount({CaseID},{GroupField})


Steve Phillips, Crystal Reports Trainer & Consultant

http://www.smptraining.co.uk

 

[CJP12711]

I tried this, but it says the count (ItemID) cannot be subtotaled... Any ideas why?

 

[SmpTraining]

{ItemID} needs to be replaced with your data field as does {CaseID} and {GroupField}.

If you have added the appropriate subtotal or totals to the report (i.e. the distinct count of cases and the count of items) then these will appear in the list of report fields in the formula editor.

Steve Phillips, Crystal Reports Trainer & Consultant

http://www.smptraining.co.uk

 

[CJP12711]

Yes - I've added my own field but get that error...

 

[SmpTraining]

The devil will be in the detail....

If your ItemId field is a database field then there should be no reason you can't count it. Can you check the datatype in case there's anything strange there (for example you can't count MEMO fields).

If this is okay, are you just using the Insert -> Summary menu option or something else?

Steve Phillips, Crystal Reports Trainer & Consultant

http://www.smptraining.co.uk

 

[CJP12711]

OK - I got past that issue. But the average seems to be for all data, rather than grouping by site and type.

I have a grouping for Location, and one for CaseID.

In the footer of the Location, I have the following formul fields:

StentCounts: count({INVENTORY.ITEM_NUMBER}, {CATH_PROC_V.LOCATION}) (this should be 74 for the location)
CaseCounts: distinctcount({CATH.CASEID}, {CATH_PROC_V.LOCATION}) (this should be 27 for the location)

The @CaseCounts formula is correct. The @StentCounts is not correct. I can't see why not... It's showing as 40, rather than 74, but can't see what the issue is.

I really appreciate your help!

 

[SmpTraining]

Is it possible the {INVENTORY.ITEM_NUMBER} has null values in it? If so, count a non-null field instead such as {CATH_PROC_V.LOCATION}.

I know it seems odd to count this column when you're grouping on it but it obviously exists and a count will work on any non-null field.

Steve Phillips, Crystal Reports Trainer & Consultant

http://www.smptraining.co.uk

 

[CJP12711]

There are no null values in this field, as it is required for any record entry...

 

[lbass]

Try inserting a running total in the detail section that does a count of inventory item number, evaluate for each record, reset on change of group: location. Then observe the results.

Are you using any group selection criteria? Any section suppression?

-LB

 

[CJP12711]

Thanks lbass, I missed that last step, which was the reset on location. They are showing up correctly now!.

The last question would be how to incorporate this into a cross tab report, if it's possible at all. Down the left side rows, there is an item category. The columns across the top would be the procdate by month, and the summarized fields would be the count of items, distinct count of cases, and...? How do I put the average in here? Thanks again for your help!

 

[lbass]

You should have mentioned the crosstab upfront as that requires a different approach.

What about location? Should that be built into the crosstab? Where? As another row?

-LB

 

[CJP12711]

Sorry - this was a later addition. I have a group for location, so that each location will have its own crosstab.

 

[lbass]

Please follow the steps in thread767-1667901. The Count summary for item# would be the equivalent of {@Formula1}, (variable "txn"), and the Distinctcount of case ID would be the equivalent of {@Formula2}, variable "adt".

-LB

 

[CJP12711]

Thanks! I got this to work correctly in the group footer section, but I'm not seeing how to place it in the cross tab correctly. When I add it, the value shows 0.00 rather than the 1.48 that I'm expecting...

 

[lbass]

This referenced solution is not intended for anything but a crosstab. Please identify the steps you took in the crosstab. You are not placing ANYTHING in the crosstab from outside the crosstab except the holding formula {@0}. Everything else is occurring in the formatting formula areas for field suppression and 'display string'.

-LB

 

[CJP12711]

My cross tab looks like this:

Rows: @ItemType
Columns: @ProcDate
Summarized Fields: {@0}

What summary calculation should I use on the @0?

 

[lbass]

You have to add the other two fields as summaries #1 (count of inventory item #) and #2 (distinctcount of caseID. {@0} is your third summary and it doesn't matter what summary you use, because this summary is just a holder for a value you will display using the "display string" feature.

Once you have all three summaries added, follow the steps in the referenced post.

-LB

 

[CJP12711]

Perfect - that worked! Thanks so much for your help!!!