portioning days of a contract by years

[JamesFlowers]

hi,

I have a set of data that has a
start_date
end_date
Value_of_Contract

I am looking to place the length of the reports in days in each yearly buucket and then assign that percentage of days in the year to the value.

eg

31/06/2012 to 30/09/2013 Val =1000000

how many days in 2011 , 2012 and 2013

eg
185 days in 2011
366 days in 2012
180 days in 2013




I have the length of contract in days , so can assume val/days is a multiple , but its the bucketing I am having issues with, any advice?


Many thanks

James


James Flowers
Crystal Consultant

 

[lbass]

Once you have this breakout, what do you want to do with the buckets? Display? Or work with them in some way?

-LB

 

[SkipVought]

31/06/2012 to 30/09/2013 Val =1000000

how many days in 2011 , 2012 and 2013

eg
185 days in 2011
366 days in 2012
180 days in 2013

HUH!?
YR DYS VAL
2012 185 =Val*DYS/SUM{DYS}
2013 272


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JamesFlowers]

@lbass , I want to use the number of days to display a value of the (contract/divided by total days)*days in bucket, and also show the amount of days in a year (for a separate KPI)

to get a value of contract per year according to the length.


@Skip thats the second stage , its the bucketing of the days in a year that the contract runs over.

Thanks.

James Flowers
Crystal Consultant

 

[RTag]

what is your database type?

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

 

[lbass]

You have a typo in your date, which is what Skip was referring to. I think you meant: 31/06/201[red]1[/red] to 30/09/2013.

Try a formula like this:

datevar st := {table.startdate};
datevar end := {table.enddate};
numbervar yr1;
numbervar yr2;
numbervar yr3;
if year(end)>year(st) then
yr1 := datediff("d",st,date(year(st),12,31)) else
yr1 := datediff("d",st,end);
if year(end)>year(st)+1 then
yr2 := datediff("d",date(year(st)+1,1,1),date(year(st)+1,12,31)) else
yr2 := datediff("d",date(year(st)+1,1,1),end);
if year(end)>year(st)+2 then
yr3 := datediff("d",date(year(st)+2,1,1),date(year(st)+2,12,31)) else
yr3 := datediff("d",date(year(st)+2,1,1),end); //etc.

Then you could display the results in separate formulas:

whileprintingrecords;
numbervar yr1;

whileprintingrecords;
numbervar yr2;

//etc.

You could probably simplify the formula and make it dynamic by using arrays, but if you know contracts only extend a certain number of years, then you can just use th above formula.

-LB

 

[JamesFlowers]

@rtypw , its oracle

@lbass , contracts can extend on many years , or just be one , how would adding an array help here?

thanks

James Flowers
Crystal Consultant

 

[lbass]

How many years? Even if it were 10 the above option wouldn't take that much time.

-LB

 

[JamesFlowers]

LB , undetermined at the moment , but as ever the 'design' committee are changing there minds , even after a spec doc was written for three years , so thats what I am going for at the moment , your formula worked superbly , many thanks

James

James Flowers
Crystal Consultant