CUMULATIVE CHART QUESTION 1

[edison1135]

I think I must be missing something simple here, but I'm having trouble figuring out how to build a line graph that shows the cumulative dollar total by quarter. I would also like a second line graph in the same chart showing the cumulative pipeline remaining by quarter.

I'm using Crystal 7.0 and have an MDB database. To identify each quarter, I'm using a formula that looks at the Award Expected Date similar to the following:

If ({BID_SUBJ.AWARD_EXP_DATE}) >= DateTime (2002,07,01) and
({BID_SUBJ.AWARD_EXP_DATE}) <= DateTime (2002,09,30) then
&quot;2002 3rd Qtr&quot; and so on for each quarter.

My report uses the above formula as a group header, and the report separates the wins by quarter.

A second formula separates the leads into wins or pipeline as follows:

If {STATUS.DESCRIPTION} in [&quot;Won Booked&quot;, &quot;Won Not Booked&quot;] then &quot;Wins&quot;
Else
If {STATUS.DESCRIPTION} in [&quot;Prospect&quot;, &quot;Stage 1&quot;, &quot;Stage 2&quot;, &quot;Stage 3&quot;, &quot;Stage 4&quot;] then &quot;Pipeline&quot;

This formula is used in second group header, and the report lists cumulative dollar amounts. But when I try to include a line chart to track the cumulative dollars by quarter, instead it shows the sum for each quarter.

Thanks for the assistance.

 

[kenhamady]

In your 4 date formulas use January 1st as the starting point, so that each total is cumulative. Then use the totals of these formulas in your chart. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[edison1135]

Ken, Thanks for the quick reply - I gave you a star. I changed the date ranges to include the previous quarters, but it still doesn't report cumulative amounts. I believe the Q1Count formula only assigns a label to the chart, but I could be wrong.

My chart is built on the following conditions: On change of @Q1Count and @Description, Show Sum of First Year Amount (The first year amount is a field) The chart program automatically places the Sum in front of my field. I tried taking out the sum, but then the chart blanks out.

The resulting chart shows the two lines I'm looking for, but gives quarterly totals, and not a cumulative total.

Thanks,

 

[kenhamady]

Not sure CR7 will do this. But you can't use this technique if you use an &quot;on-change of field&quot;. Try a Detailed chart intead of a group chart.

Or you will have to create 2 running totals to get cumulative values. I think v7 supported running totals in charts, but v7 may make this a more difficult task. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com