Variable Equation

[gkrenton]

In my mind I know what I want to accomplish but I'm not an advanced user enough to know how to get there.

Have a start & cancel date on a purchase order. If the range is greater than 1 month I want to evenly split the cost value over the 2 or more months in which it covers. The end result is a report that shows how many dollars are expected to be spent/received in each month thus a grouping by month.

My one inclination is to create a variable based on the date range(s) which I've done with the following equation.
numberVar OTBcode;
if {@OTBdays} <=45 then 1 else
if {@OTBdays} <=62 then 2 else
if {@OTBdays} <=91 then 3
else 4

But then my brain stops & can't figure out how to use this variable other than saying if the otbcode = 2 then cost/2 but then I don't know how to address the allocation part of what I'm trying to accomplish.

Yes I know I'm in way over my head but it seems like it should be 'doable'.

Thanks as always.

Gina

 

[synapsevampire]

My mind doesn't fully understand what you want to accomplish.

Please post version information for Crystal and the database, example data and expected output to help clarify.

It sounds like you have a value, and you want to divide it by the number of months difference between the start and cancel dates.

Does this mean that start of Jan 31 and cancel of Feb 1 would be split?

Anyway, try:

{table.value} / (datediff(&quot;M&quot;,{table.startdate},{table.enddate})+1)

-k

 

[gkrenton]

I'll try to clarify:
Basically I have purchase orders that can have delivery windows of 1, 2, 3, or 4+ months & I'm trying to sum up the total cost on order by month delivery window.
A one month window is easy but when the delivery window exceeds 1 month I want to evenly allocate a percentage of the order across the months deliveries will occur
IE
Start Date = 12/01/2003
End Date = 1/31/2004
Purchase order Cost = $1000.00

Thus I would want to allocate $500.00 to December & January to know the estimated value of merchandise arriving when.
Where I'm stuck is how to create a value like the above & allocate it to a different period(s). I'll try your suggested idea above to see if it moves me along on the the process and if the above clarifies it any more in your mind & you have other suggestions I'd appreciate it.

Thanks

Gina

 

[Madawc]

For Crystal 8.5, you could find the number of months by compairing start and end dates, e.g. by a formula field called @Month_Span
DateDiff (&quot;m&quot;, {StartDate}, {EndDate})

Then do another formula field: @Month_Purchase
If @Month_Span = 0
then {purchaseorderCost}
else {purchaseorderCost}/@Month_Span

Does this solve it?

Madawc Williams
East Anglia, Great Britain

 

[GJParker]

Gina

The way i see it is that your problem isn't splitting the value across the number of months, but allocating that value to each of the months to get you a total for each of the months.

I'm sure this is possible using a combination of array variables, but depending on the setup of your data and the final report, another way may be to create a months table in your database and link this to the purchase order in such a way as to bring in a record for each fo the months the purchase order covers.

i.e.

Month
01/01/2003
01/02/2003
01/03/2003
..
..
01/12/2003

then link this to the purchase order

StartDate >= Month and CancelDate <= Month

This should return a record for each month the purchase order covers, you can then create a group for each month from the months table and use SV's formula to give you the values for each purchase order i.e.

{table.value} / (datediff(&quot;M&quot;,{table.startdate},{table.enddate})+1)

HTH

I have a sample report showing the technique if you want a copy.






Gary Parker
Systems Support Analyst
Manchester, England

 

[gkrenton]

I think Gary did capture my 'dilemma' best in how to allocate the value. If you have a sample report with what you're discussing on your response I'd love to see the formulas so that I can try to emulate -

Do you want to post or email it directly?

-gina

 

[Ngolem]

Personally I like Madawc's approach...he defines the number of months and the resulting cost/month very nicely.

Gina - What you have to do for us is define how the report is supposed to look...You have not shown us this or how the cost is calculated or if it is infact a single value.

It is not clear if the report is one which is defining

1) a single purchase order or group of purchase orders and the output is something like this for each

Start Date = 12/01/2003
End Date = 1/31/2004
Purchase order Cost = $1000.00
Monthly Cost : December/2003 January/2004
$500.00 $500.00

This is easily done in the Purchase order footer section with no real requirement for arrays since the cost is equal for each month

2) or if the report is grouped by Date and then you want to have an idea of your total costs for all currently valid purchase orders through this time period.

This is a much more complicated report requiring a lot of thought

Show us what you are looking for....personally Madawc has given you the answer to the type (1) senario



Jim Broadbent

 

[GJParker]

If you supply an email address Gina I'll send ya the report



Gary Parker
Systems Support Analyst
Manchester, England

 

[gkrenton]

Sorry for the confusion in my mind it's as clear as mud so typing it seems to be the same result and I certainly appreciate the help.

Basically I'm trying to sum the value of all Open purchase orders by dept & date for an ongoing analysis.
Continuing to use the ongoing example of

Dept 1
Class 10
Start Date = 12/01/2003
End Date = 1/31/2004
Purchase order Cost = $1000.00


What I have thus far is a report where I can get the intended results but can only allocate the onorder dollars into a 1 month period.

Thus :
{dept}, {class}, sum{onorder$Cost}, sum{onorder$retail}
grouped by {dept} and {POdate}.
So my result looks like this

Dept1, 1/31/04 Class10, $500, $1000

This works fine but doesn't neccessarily allocate dollars as they are likely to flow into our business.

What I'm looking to do is take it one step further:

{dept}, {class}, sum{onorder$cost}, sum(onorder$retail} grouped by {dept}, {POStartDate}, {POCancel date}

where the onorder$cost & onorder$retail is equally allocated to the the months including & between the POStartDate month & the POCancel date month.

So the result I'm looking for is

Dept1 12/03 class 10 $250, $500
Dept1 01/04 class 10 $250, $500
Thus I think this falls into Jim's 'its the more complicated and needs more thought' and I may just have to settle for what I've got & allocate to only 1 month & deal with it.
Meanwhile Gary my email is below so I can take a look at your idea & see if I can integrate it with Madawc's idea.

Thanks all -

gina
gina@rentonww.com

 

[Ngolem]

I used to be a sales manager...that is why I thought this should be much more complicated then what you were describing initially.

But it is doable....just requires more thought as to how the report is laid out.

If you want we can go through this thought process...now that you have provided us with more information.

First of all it would be nice to somehow define your date range for results.

1. do you want the date range to be that of a complete calendar year?
OR

2. do you want the date range do go across from one year to the next...say for a 12 month period

OR

3. (much harder) do you want the date to range from the earliest {StartDate} to the last {EndDate} no matter how many months this entails?

The first 2 choices are relatively easy using arrays (one array for each purchase order and another for the grand total)...Then you can see where you stand for say the next 12 months.....(make the startDate for the report a parameter so the user could define which 12 month period they want to see (eg. Perhaps you want to see 12 months of costs from October/2003 to September 2004 for all open orders. So if an order was open say since July 2003 you would only consider costs from October 2003 on...and similarly if an order was ongoing past Sept 2004 you would ignore those costs for the time being.

Take it one step at a time to define your report

Jim Broadbent

 

[gkrenton]

For our purposes a 12 month 'rolling' number is sufficient since once the month has passed the open dollars aren't going to be 'spent' and they are recorded elsewhere and these numbers are being used for 'forecasting' purposes.



- gina