6 month rolling percentage in Crystal Reports 2

[crimsntyd]

Hi-
I need to know how to calculate a 6 month rolling percentage in my Crystal Report and graph it. The two fields in the formula are deliveries and returns (called recycles). So, if my data looks like this (with the data starting in August):

Aug: 6 Deliveries, 0 recycles -----> 1-(0/6) = 100%
Sept: 9 Deliveries, 3 recycles -----> 1-(3/15) = 80%
Oct: 12, 1 -----> 1-(4/27) = 85%
Nov: 6, 1 -----> 1-(5/33) = 85%
Dec: 9, 0 -----> 1-(5/42) = 88%
Jan: 9, 0 -----> 1-(5/51) = 90%
Feb: 9, 1 -----> 1-(6/54) = 89%
Mar: 15, 2 -----> 1-(5/60) = 92%

I'm trying to be able to automate the calculation of the 6 month rolling percentage, as demonstrated on the right of the data above. Anyone have any ideas? Thanks in advance...

 

[Turkbear]

Hi,
Look at the help file son Running totals and how to determine when to reset them..

It would take too much detail to specify the exact method for your data but that info should help you get started..

( In very broad terms, you need to keep a runing total of each field ( deliveries and recycles), create a formula that computes the % from these and set up a formula that will determine when 6 months has been totaled and reset the running totals then)







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

Here is a variation on a solution by bdreed35 that should work for you:

numberVar array Accumy;
numbervar array Accumz;
numberVar x;
numberVar y := 0;
numbervar z := 0;

if OnFirstRecord then
( ReDim Accumz [1];
ReDim Accumy [1])
else
( ReDim Preserve Accumz [UBound(Accumz)+1];
ReDim Preserve Accumy [UBound(Accumy)+1]);

Accumz [UBound(Accumz)] := {table.recycles};
Accumy [UBound(Accumy)] := {table.deliveries};

If UBound(Accumy) < 7 then
y := Sum(Accumy)
Else
(
for x := (UBound(Accumy)-5) to (UBound(Accumy)) do
(
y := y + Accumy [x];
);
y;
);
If UBound(Accumz) < 7 then
z := Sum(Accumz)
Else
(
for x := (UBound(Accumz)-5) to (UBound(Accumz)) do
(
z := z + Accumz [x];
);
z;
);

z % y

-LB

 

[crimsntyd]

Thanks, lbass! Works GREAT! Now, how do I graph it in Crystal? It doesn't show up in the list of available fields in chart expert...

 

[lbass]

I just tried using the above formula with the approach outlined in this document to develop the chart:

http://support.businessobjects.com/communityCS/TechnicalPapers/cr9_charting_print_time_formulas.pdf

For the {@show value} formula I used:

whileprintingrecords;
stringvar showval := showval + totext({@rollingformula},2,"") + ", ";

I struggled with this a bit before getting it, so let me know if you need help.

-LB

 

[crimsntyd]

I'm struggling too. I'm trying to graph by month, so I used totext({db.Month}) in place of {Customer.Customer Name} in the example, but I can't get the chart to show up on my main report. It's blank. Why did you have to go to the ", " instead of the "^" as in the example? Just curious. Thanks for all your help.

 

[lbass]

I used this for {@onchangeof}:

whileprintingrecords;
stringvar onchof := onchof + totext(month({table.date}),"00")+"-"+totext(year({table.date}),"0000") + ", ";

Then in the record selection of the subreport, I used:

totext(month({table.date}),"00")+"-"+totext(year({table.date}),"0000") in split({?Pm-@onchangeof},", ")

But in the chart expert itself, I used {table.date} as the "onchangeof" field and I selected Order and chose "print on change of month".

I just used ", " instead of "^" out of habit, and I wasn't using any comma dividers in my numbers, so that worked.

-LB

 

[crimsntyd]

I've still got nothing. My date format is already in mm/yyyy. Could that be hosing something up? I gotta be missing something simple here.

 

[lbass]

What do you mean by your date format? What are the fields you are working with? It looks like you have a field {db.month}--is that a number field from 1 to 12? How do you determine the year?

-LB

 

[crimsntyd]

Sorry for the confusion. My {db.Month} field looks like 08/2005, 09/2005, etc.

 

[lbass]

So it is a string field? Or a date field that you have formatted to appear as you've shown?

-LB

 

[crimsntyd]

It's a date field. Format mm/yyyy from an Access Database. Sorry I haven't given more information. I have version XI.

 

[lbass]

If the datatype is date, then the format is irrelevant--the format is only determining the display. You will need to use formulas like mine in my post of 14:05. Just substitute your field name for {table.date}. Then let me know if you get the chart to work.

-LB

 

[crimsntyd]

Here's my @ShowValue:

whileprintingrecords;
stringvar showval := showval + totext({@6Month},2,"") + ", ";

Here's my @OnChangeOf:

whileprintingrecords;
stringvar onchof := onchof + totext(month({db.Month}),"00") + "/" + totext(year({db.Month}),"0000") + ", ";

Here's my subreport record selection:

totext(month({db.Month}),"00")+"/"+totext(year({db.Month}),"0000") in split({?Pm-@OnChangeOf},", ")

And here's my SubShowValue:

ToNumber(Split({?Pm-@ShowValue}, ", ")[RecordNumber])

I also did as you suggested with the chart expert, putting {db.Month} in the "On Change of" field and having it print on change of month.

I've gone over it a couple of times now, and I don't see any differences between my code and yours, other than I put a "/" in between the month and year on my dates where you had a "-", and my subreport is still not showing up on my main report.

As a side note, the reason I was asking about the line-column graphs is because I'm graphing "@6Month" (line) along with deliveries (column) and recycles (column), but I haven't got that far yet with this new method. I had the columns before but no @6Month.

 

[lbass]

I don't see any differences either. When you set up the subreport links, did you remove the checks from "select data in subreport based on field" for both links?

-LB

 

[crimsntyd]

Yep. I tried a couple of things this morning already, since, like i said in the last post, I'm going to have to graph deliveries and recyles as bars and the @6Month as a column. When I try to put @SubShowValue in the "Details" section, it crashes, goes to the formula editor (for SubShowValue), and gives me a message saying "the string is non-numeric". I don't know if that has anything to do with it or not.

I can't get the graphs of the max of deliveries and max of recycles to work either in the subreport. I had them OK as a chart in the main report before we found out we had to do this method. Thanks again for the help.

 

[lbass]

Can you verify that your data in the main report in in the detail section? When I did my version, I used group level data in the original formula and in the subreport, I then had to insert a group on date (monthly) and change the showvalue formula to use groupnumber instead of recordnumber. I didn't mention this, as I thought your data was at the detail level. Now that you have mentioned maximums, etc., I wonder if this is true.

-LB

 

[crimsntyd]

I guess I'm dense, but I really don't understand your last post. I inserted a group {db.Month} and grouped by month. My original formula (@6Month) and recycle and delivery data is in the Detail section, and working properly (it also works OK without a group). I inserted a group in the subreport and put @SubShowValue in the detail section (and suppressed it). My graph/subreport is now showing on the main report (yee hah!), but there are 178 entries per group in the details section, and it's summing all of them, so instead of 0.87, I get 178*.87 for my @SubShowValue (I clicked on "don't summarize" in the chart expert). Obviously, that number increases substantially if I sum it. The reason that 178 is important is that it's the number of part numbers that I keep track of with this delivery/recycle data. I have a parameter field that gives me the 1 out of the 178 that I want. So, what do you mean by using "group level data in the original formula and in the subreport", and do I have to possibly put the same parameter filter on the subreport somehow?

 

[crimsntyd]

Got it. I had to pass the parameter field to the subreport in order to get the one that I wanted. Dude, you are a LIFESAVER. Thank you SO MUCH for all your help. It's amazing that as powerful as CR is, you have to jump through these hoops to graph and automate something that's so simple in Excel. Take care!