How to count variables <> 0 for use in average formula?

[Jacque]

My report displays yearly usage by month. Variables are used to sum and display the total usage per month, it's essentially a manual crosstab. We display total usage and costs.
My problem is determining average usage (total usage/number of months showing usage). For example, I have an item which was issued in Jan (8 units) and Mar (2 units), total usage (10 units) and divide by (2 months), I should get 5 but I haven't been able to figure out how to count the number of months showing usage. The dates in the table show daily transactions and are converted within the report options to string fields (long story - cannot change this aspect).
I can get an average based upon the number of transactions but I need it based upon the number of months showing usage.

I'm using CRWv8.5Pro and the db is Sybase 7.
Any suggestions welcome!
Thanks!

 

[Dond12]

We run a similar kind of report and use an counting variable that increments by one for each month that the total in a particular field is greater than zero. Then another variable simply resets the counter to zero when the year changes. You could try something like this.

 

[Jacque]

Dond12,
I must have done something wrong because I could not get it to count correctly using a counter. But I finally found a work-around to solve my problem.

I ended up creating 2 formulas to use in a running total.
First formula contains:
month(DTSToDate ({Table.MonthOfYear}))

Second formula:
{Table.netUseQty} <> 0

and then the running total does a distinct count of the first formula and evaluates on the second formula and resets on change of the item.

 

[Ngolem]

Jacque

How is your report grouped? By Year then by Month..then other product related groups?

I usually do my counting manually with formulas

I can help you but you will have to set out the structure of your report JimBroadbent@Hotmail.com

Reward good advice with a star, it reinforces us and helps others find answers to their problems.

 

[Jacque]

Jim,
First let me tell you that I didn't design this, it's been thru at least 4 different developers or report writers, it's just my turn to maintain it . But here goes:

Group1 Station
Group2 (formula) Item Name/ID
Group3 is pocket (the bin the item is maintained in)
Group4 is the date/time field MonthOfYear
(example data 2002-05-03 00:00:00.0).

Group header1 displays the station name.
Group header2,3,4 are suppressed.
Details are suppressed.
Group footer 2a displays the static item info and the total usage, avg usage and cost info.
Group footer 2b displays the total qtys per month.
Group footer 1 displays the Station totals.
and the report footer contains grandtotals.

 

[Ngolem]

ok...first off I would convert the date/time group4 to a date value....unless time is critical as it sometimes will create &quot;extra&quot; groupings for you.

ok...I suppose you are setting up arrays to catch the monthly totals for later display

I would assume you have an initialization formula to set these totals to zero...placed suppressed in the Group 2 header. MAKE SURE that this has a test to see if the header is repeated...since on a new page you may re-zero variables

If not inrepeatedgroupheader then (reset the variables)

now add to that initialization formula a variable for
TotalMonth

numberVar TotalMonths := 0;

NOW in the Group 4 header (suppressed) just put a formula to count the months.

WhilePrintingRecords;
numberVar TotalMonths;

TotalMonths := TotalMonths + 1;

then use this variable in your averages later

This is the way I always do it (a creature of habit once things work) but a simpler way might be to eliminate what I just said and get the DistinctCount of the months in Group 4

the formula escapes me right now since I don't have Crystal up and I don't normally do it that way....anyway hope this helps.


JimBroadbent@Hotmail.com

Reward good advice with a star, it reinforces us and helps others find answers to their problems.

 

[Jacque]

Jim,
Can't change Group4.
Yes on the arrays,
yes on the initialization formula in group header 2.
No on the inrepeatedgroupheader portion. Group2 was developed with keep group together as was the section formatting for Group2 to keep from orphaning a group section information, so do I still need this?

Your counting formulas are the same as I used previously and for some unknown, at least to me, they didn't work. I tried again following your notes including the inrepeatedgroupheader test and it still doesn't work for me.

I can't even determine what it is counting, for the example above, when I use the TotalMonths var, I got 1 as the average, for another item with just one transaction, I got 0 as the average. ??? At least the running total works, so I guess I'll let that stand until I can figure out where I went wrong.

Thanks for your help, people that are generous with their talents and skill are greatly appreciated.

 

[Ngolem]

post your actual formulas...so we can have a look at them JimBroadbent@Hotmail.com

Reward good advice with a star, it reinforces us and helps others find answers to their problems.

 

[Jacque]

@initialization location groupheader2
whileprintingrecords;

NumberVar Month1Count;
NumberVar Month1Stockout;
numberVar vTotalmonths;

if not inrepeatedgroupheader
then vTotalmonths := 0;
if not inrepeatedgroupheader
then Month1Count :=0;
if not inrepeatedgroupheader
then Month1Stockout :=0;


@Monthcounter location groupheader4
whileprintingrecords;
numberVar vTotalMonths;

vTotalMonths := vTotalMonths + 1

@AvgUsageVar
numberVar vTotalMonths;

If (Sum ({Table.netUseQty}, {@Item Name/ID}))=0 then 0
else
if {#CountTotalUseage}=0 then 0
else
Sum ({Table.netUseQty}, {@Item Name/ID})/vTotalMonths;

I removed all suppressions for this testing to determine where I had gone wrong but what appears to be happening is that it is counting each date instance, not month. The item with the zero usage was actually .33 but was rounded down to zero.
Thank you for all of your help.

 

[Ngolem]

you are missing a &quot;WhilePrintingRecords&quot; in this formula

@AvgUsageVar
WhilePrintingRecords;
numberVar vTotalMonths;

If (Sum ({Table.netUseQty}, {@Item Name/ID}))=0 then 0
else
if {#CountTotalUseage}=0 then 0
else
Sum ({Table.netUseQty}, {@Item Name/ID})/vTotalMonths;

That may fix it...I am not sure but you should be consistant in the formulas. JimBroadbent@Hotmail.com

 

[Jacque]

Oops, missed that one. I corrected the formula, reset the sql query, verified the db, refreshed the report but no luck, it still gives me the wrong count. As long as the running total works, I'm ok.

Jim, thanks for all your help.
Jacque

 

[Ngolem]

here is your problem I think:

@initialization location groupheader2
whileprintingrecords;

NumberVar Month1Count;
NumberVar Month1Stockout;
numberVar vTotalmonths;

if not inrepeatedgroupheader
then vTotalmonths := 0;
if not inrepeatedgroupheader
then Month1Count :=0;
if not inrepeatedgroupheader
then Month1Stockout :=0;

most of this probably belongs in Group2 header but the reset of the totalMonths belongs suppressed in Group3

try this:

@ResetTotalMonths (Placed in Group3 suppressed)

whileprintingrecords;
numberVar vTotalmonths;
if not inrepeatedgroupheader then
vTotalmonths := 0;

@initialization (**revised** location groupheader2 supress)
whileprintingrecords;

NumberVar Month1Count;
NumberVar Month1Stockout;

if not inrepeatedgroupheader
then Month1Count :=0;
if not inrepeatedgroupheader
then Month1Stockout :=0;

that should do it


JimBroadbent@Hotmail.com