Showing value of a contract split by the length

[adam7]

Hi there, I'm using crystal 8.5 with a SQL 7 backend. I'm currently trying to obtain figures for a management report and am having difficulty getting my head around this issue.

I have multiple records for a product, each with a value and a start and end date. If I had a contract worth £12000 and with 1 year remaining on the contract I want it to show each month of the contract remaining with £1000 against each month.

Within the same group I might have another contract that is for £1200 but over 24 months, so I'd like the date section to carry on and show £500 for each month, but for it to show £0 in the contract one months for the 2nd year.

After having tried many different ways is there a simple way to do this?

Thanks.

 

[GJParker]

Please give examples of your input data and how you require the output to look on the report.



Gary Parker
MIS Data Analyst
Manchester, England

 

[adam7]

Hi thanks for replying. It's group by the contract name which is a formula (all works fine). At the moment the End date of contract 1 is Jul and contract 2 end date is March.

I'm having to write each formula to say if end date in Jan then total etc.... Currently I can get it to show each month as the value that's left on the contract, so it would go from £700 to £100 (See example below. I want it to show an even amount for each month. This would be simple if all contracts were the same length, but they're not.........This is what I want it to look like.

Jan Feb Mar Apr May Jun Jul


Contract 1 -
Value £700 £100 £100 £100 £100 £100 £100 £100

Contract 2 -
Value £200 £100 £100 £0 £0 £0 £0 £0


Cheers
Adam

 

[lbass]

First group on {table.contractno}. Then create formulas like the following:

//{@Jan}:

if {table.contractstart} <= Date({?year},02,01)-1 and
{table.contractend} >= Date({?year},01,01) then
{table.contractamt}/datediff("m",{table.contractstart},{table.contractend})+1 else 0

//{@Feb}:
if {table.contractstart} <= Date({?year},03,01)-1 and
{table.contractend} >= Date({?year},02,01) then
{table.contractamt}/datediff("m",{table.contractstart},{table.contractend})+1 else 0

...assuming you might want to set up a parameter for the year of data you want to look at. If your data crosses calendar years, you then use {?year}+1 or {?year}-1 to represent that in the formulas.

I think your question changed since your first post above. Your initial question seemed to be how do you make the result zero when the contract amount crosses into the next fiscal year. If you still have that issue, please explain why you would be including dates in your report where you want zero results. Wouldn't all contracts past that particular point also have to be zeroed out?

-LB

 

[adam7]

Hi there, thanks for the response. However, creating groups by the month isn't the problem really. I'd group by say contract ID to give me a list of all. Then total each line to give me the contract value. Also add the contract length to each line.

Then across the top I'd write headings of Jan 04 Feb 04 etc...

What I then want to do is take the contract value of each one and have a figure split out evenly for each month for as long as the contract is valid for. So some would run till March, some might run till December. I want to show March to December for instance, and on the contracts that end in December for them to be split evenly amoungst the totals, whereas the ones that end in March they would be split evenly until March, and then be £0 from April to December.

Does that make sence? It would looks something like this, and I'm grouping by the contract 1 & 2 etc....


Jan Feb Mar Apr May Jun Jul Tot


Contract 1 -
Value £700 £100 £100 £100 £100 £100 £100 £100 £700

Contract 2 -
Value £200 £100 £100 £0 £0 £0 £0 £0 £200


Hope this helps. It's kind of a group I'm looking for, but almost want to group across the page as well as down.

Thanks

 

[lbass]

I wasn't trying to address getting the months across, although I believe you have to use this method to do this. Did you try my suggestion, because I still believe it is what you need to do. If you try it, and don't get the results you want, please provide a sample of your results and explain how they are different from the results you would like to see.

-LB