In short, I want to be able to have the report return any items that have an amount that falls within a specific range.  I add up the total amount of one item for one month, and I then compare that to the past X number of months total. Investigation Month vs. Previous Months Total

 

(For example: December (Investigation Month) Total 500 compared to November, October, Septemeber totals added together... let's say 2,000.  I then calulate the average of the previous month. Since there are 3 previous months being calculated, we will divide 2000 by 3.  From there I will generate a variance range that is specified in my parameters for which I wan to investigate. Let's say 20% variance.  So then I will multiply (2000/3)*.20 and then Add and subtract the [(2000/3)*.20] from the original (2000/3) average.    So in this example, the average = 666.66, and the variance would be 133.33.  So then the range would be  range would be 533 to 799.  And since the december total was 500, this item would not fall in range.

 

From there, I wanted crystal to only bring me back items in my database that don't fall in the variance range.

 

The fields are first placed in the Lowest group (group 4) and grouped by month.

 

The Investigation Month Item amount field is titled the SUM of @Total Amount: 

@Total Amount = IF (Month({@Date})) = {@Investigation Month #} and YEAR({@Date})=2011 then {PORECLINE.ORIG_UNIT_CST}*{PORECLINE.ENT_REC_QTY}

 

The previous month totals are calculated first by each month alone:

@Total Amount - Look Back Month = IF ({@Date}) < {@Date Range End} AND {@Date}>= {@Date Range Difference} then {PORECLINE.ORIG_UNIT_CST}*{PORECLINE.ENT_REC_QTY}

 

The totals of each PREVIOUS month, is then summarized with the sum total being placed in GROUP 3 (which is grouped by item)

 

The average is then calcuated on the same group:

@Look Back Average = Sum ({@Total Amount - Look Back Month 1}, {PORECLINE.ITEM})/{?Look Back Month}

{?Look Back Month}= the number of months previous to the investigation month.

 

The average is then multiplied by the variance quantity to calculate the variance:

@Percent Variance = ({?%Variance}*.01)*{@Look Back Average 2}

(the .01 is there so we can cleanly convert to % format)

 

We then calculate the range by adding and subtracting the @Percent Variance from the @Look Back Average:

@Above%={@Percent Variance}+{@Look Back Average 2}

@Below%={@Look Back Average 2}{@Percent Variance}

 

And FINALLY we would want crystal to return only the items that have:

@Total Amount - Investigation Month in @Above% to @Below%

 

Any which way I try, I always end up seeing this formula cannot be used because it must be evaluated later.

 

Please let me know if there is anything else I can provide.

 

Thanks in advance.

 

 

 

 

 

 

The Range is generated by