Problem with Sum Formula

[TanmedIT]

let me give you a little history on this issue. I have 2 Database link together. One is a progress database and the other is the excel spreadsheet. One of the progress tables (part_master) has a field called "product_line" which I have linked to the excel spreadsheet that has the following information (it goes to decemeber, but for the sake of space, I will go to April):

ProductLine January February March April
100 244.77 202.83 215.97 265.41
120 3.45 5.75 6.90 13.88
140 40.75 70.70 90.82 114.35
130 67.40 75.34 71.61 89.67
800 - - - -
500 - - - -


My issues is trying to get the cummulative sum to show for any given month. I have a formula to show the budget for a product line for a given month, but when i run a "Sum" summary on that formula field it gives me a crazy amount because it is summing for very instance of the occurance (which is the appropriate thing for it to do).

But I need it to only calculate the sum for every distinct occurance and I am drawing a blank.

Thanks in advance and let me know if you need more information

 

[synapsevampire]

It's best to post:

Crystal version (1st question from any tech support person)
Example data (show what's in the tables)
Expected output

I tried to figure out which the above was...

Rather than saying sum for every distinct, why not show what you have, and what you want, and then if you still wish to explain more, then add what you tried and does not work.

-k

 

[TanmedIT]

Sorry, Its a little early on the West Coast:

1) Crystal XI
2) I thought I did
3) I want to show the cumulative sum for the month (example: April will be 265.41+13.88+114.35+89.67 which is 483.31)

I ran a "Sum" Summary on my @Monthly Budget formula which is:

If {Budget2006.ProductLine} = {pt_mstr.pt_prod_line} then
(
If Month({ih_hist.ih_inv_date}) = 1 then ({Budget2006.January}*1000)
else
If Month({ih_hist.ih_inv_date}) = 2 then ({Budget2006.February}*1000)
else
If Month({ih_hist.ih_inv_date}) = 3 then ({Budget2006.March}*1000)
else
If Month({ih_hist.ih_inv_date}) = 4 then ({Budget2006.April}*1000)
else
If Month({ih_hist.ih_inv_date}) = 5 then ({Budget2006.May}*1000)
else
If Month({ih_hist.ih_inv_date}) = 6 then ({Budget2006.June}*1000)
else
If Month({ih_hist.ih_inv_date}) = 7 then ({Budget2006.July}*1000)
else
If Month({ih_hist.ih_inv_date}) = 8 then ({Budget2006.August}*1000)
else
If Month({ih_hist.ih_inv_date}) = 9 then ({Budget2006.September}*1000)
else
If Month({ih_hist.ih_inv_date}) = 10 then ({Budget2006.October}*1000)
else
If Month({ih_hist.ih_inv_date}) = 11 then ({Budget2006.November}*1000)
else
If Month({ih_hist.ih_inv_date}) = 12 then ({Budget2006.December}*1000)
)

and I get 247,214,145.00 Which is wayyyy more than it should be but I know exactly why, but I can't figure out how to remedy.

I also made a Running total on the @MonthlyBudget formula to only evaluate on change of "product_line" and I get 359,810.00 which is too low

 

[TanmedIT]

Cancel that I figured it out. Thanks for making me post all that information. It made me realize my error!!

I changed Month(invDate) in the @MonthlyBudget fomula to Month(?Date) because if was taking the value for the first instance of using that for the running total, which was in January.

Muchas Gracias!!