I have been banging my head trying to figure out this impossible problem with the lin echart. I have two types date fields.
Date1 is straight from the datasource and Date2 is a Crystal formula based field that has two other date fields embedded in it.
Now, Date1 has a range between 01-01-2010 to 12-31-2010 and the formulate Date2 has a range from 01-01-2010 to 06-15-2010.
I am doing a count of a structure field {struc_ID} based on the two above date fields.
The problem is I am trying to do a cumulative count of the {struc_ID}, one that counts based off the Date1 and the other count based off Date2.
Now to combine the two date fields I wrotea a formula to be placed in th chart expert "change by"
@DateComparison:
if {Date1} in date{2010,01,01} to date{2010,12,31} then {Date1} else
if {Date2} in date{2010,01,01} to date{2010,06,15} then {Date2}
Then I sort it by Date1.
I do a running total for the {struc_ID} that will count for Date1
Then I do a second Running Total for the {struc_ID} that will count for Date2
(in this case I use a formula :
@Date2Count :
if {Date2} <= {date1} then {struc_ID} --- or if isnull({Date2}) then {struc_ID} = ""
)
then I apply both the running totals in the "Values" box in the chart expert. I chose a line chart to display the values.
I am assuming the date range is from 01-01-2010 to 12-31-2010 , but it seems like count for Date2 is extening till 12-31-2010,
which it should not, it should stop at 06-15-2010 because there are no values after this date, so how do I force the count for Date2
to show no value in the chart after 06-15-2010? I have tried several methods to come up with a solution ove the past few weeeks, but nothing worked!!!
Does anybody have any expert knowledge about how to contain a line when there are mutliple counts in a line chart within different date ranges along the
x-axis?
Please, I really do need some serious help!!...its been weeks I've been working on this!!!
Here is some sample data
struc_ID Date1
38944893 1/1/2010
63943849 1/28/2010
34709374 3/8/2010
44973949 3/18/2010
93493439 4/12/2010
45435334 4/24/2010
35354666 5/5/2010
65464456 5/23/2010
46545454 6/11/2010
56765643 7/25/2010
89898998 9/12/2010
89808993 11/13/2010
46929020 12/25/2010
74389398 12/30/2010
--------------------------------------
struc_ID Date2
53535354 1/13/2010
54754456 1/28/2010
46554446 3/8/2010
46464344 3/18/2010
35435333 4/12/2010
45435334 4/24/2010
64644646 5/5/2010
84466464 5/23/2010
84464644 6/11/2010
84456544 6/14/2010
68434574 6/15/2010
Thanks