• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 421
  • Last Modified:

Charting Dates

I have a data set that is fairly simple but forhte life of me can't figure out how to chart it the way the client wants. This has me rather fustrated because i feel like i am missing something fairly obvious.

Dataset of Students (who have dropped out of a program) with their last day of attendance.

Student Id     LastDate
100001         5/1/2005
100012         4/24/2005

Need a calendar graph to indicate trends of when in the term most of them are dropping. I would expect this would have a X axis as the date range of the term a datapoint for each week or day or month (some would have 0) and the Y would be the numbers.

In crystal it would seem a scatter chart would do this well, but I can't figure out how to get the date range for the X axis.
0
datagears
Asked:
datagears
  • 3
  • 3
  • 3
  • +2
1 Solution
 
wykabryanCommented:
In theory... you want to create a formula that does the max date for each student.  This would be his last attendance record.  Then in the chart, you want to use the formula in the "On Change of", and Show value would be the count of students.

Hope this helps.
0
 
datagearsAuthor Commented:
I have the last date of attendance stored in the dataset. "ON change of" for date field "LDA" gives me a chart but not one proportional to a calendar (i.e. there are no X datapoints that are zero) just a count for each date that someone dropped. Not helpful if I am trying to trend to see if drops are happening at a certain point in the month.
0
 
jaysin144Commented:
The X-axis will depend on the date range you want to compare against.  You mentioned that you wanted to create the chart to determine when in the term most of the dropped the class.  If you X axis was the period equal to the term of the class(es) the Y axis would plot the point at which they dropped the class.  It sounds like you not so much concerend with identifying the student, just trying to discover a pattern.  Is this correct ?

0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
wykabryanCommented:
You may want to switch to a bar or line graph. In addition if your column contains a datetime you will want to roll it up to the day.  Under On change of, there is a Order button.  You will want to change The section will be printed area to day.
0
 
datagearsAuthor Commented:
jaysin144 -

Yes that is exactly what i want BUT i can't figure out a way to spcify a date range to the X axis in Crystal. That woudl be the simple thing I think i am missing.

wykabryan -

that option was greyed out and doesn't address needing to specify a date range for the X axis.
0
 
mlmccCommented:
THe only way to get what you want is to ensure all the dates are there.  In your data set there are missing dates since there are dates when nobody dropped.

The usual way to get this is to create a table of the dates then use it as the master table and left join the pother tables to it.

mlmcc
0
 
wykabryanCommented:
Ok.. so not every day does some one drop out of a class.  mlmcc suggestion would be the only way to ensure that you get all of the dates.  There might be another way where, in the report you create the dates where you are missing the dates.  I saw it once up here, but did not book mark it.
0
 
jaysin144Commented:
What if you created a incrementing formula with say the start of the semester as the initial value and the end date of the semester. Use that as the x axis?
0
 
OutinCommented:
wykabryan:

What you meant can be done by adding any table with enough rows to your report.
Create a formula like

  CDate(RecordNumber + x)

where x is an offset in days from december 30, 1899. x = 36526 would get you started at 01-01-2000.

Then link the formula to the datefield from the table that contains the needed data.

--
Outin
0
 
mlmccCommented:
The reference you are looking for is

http://support.businessobjects.com/library/kbase/articles/c2002147.asp

Since it is a WhilePrintingFormula you will also need to reference
Charting on Print Time formulas
http://support.businessobjects.com/communityCS/TechnicalPapers/cr9_charting_print_time_formulas.pdf.asp

mlmcc
0
 
datagearsAuthor Commented:
The crystal example is clunky at best and not well written. But mlmcc get me thinking in a alternate direction. The solution (which worked amazingly well in the end) was ....

To write a stored procedure that took a date range and to create an output table on thy fly that had a count for every date in the date range (by doing a loop, capturing the count, then adding a day to the date with datadd() and repeating until the end of the range was reached) then end result was a table for every day in my date range with a count. The crystal report then used the stored procudure as it's datasource.

I think sometimes Crystal does to much for you that you forget the more simple things that you might need to program for.
0
 
mlmccCommented:
Excellent.  That is probably better than creating a permanent table with all possible dates.

Glad i could help

mlmcc
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now