Charting count of records for each recent week

[mychal]

My simple table has two relevant columns: ID and Date.

I'd like a chart of the counts of records falling within a one week period, for each of the last several weeks. The y-axis would be the counts and the x-axis would be something like "Week Minus 1", Week Minus 2", "Week Minus 3", etc.

So, the first data point on my chart would be, for example, 2 records from last week (Week Minus 1). The next would be 44 records from two weeks ago (Week Minus 2). The third would be 68 records from three weeks ago (Week Minus 3). And so on.

Thanks in advance.

 

[mychal]

I also have a slightly different issue along these lines, but they are close enough that I figured I'd put them into the same thread.

This is based on the same table as above.

I need a chart where the y-axis is record count and the x-axis is the number of records where the difference between the current date and the Date column is greater than an integer number of weeks. So my previous example, if I assume the oldest records in my table are from three weeks ago, would plot the following points: (> 3 Weeks, 114), (> 2 Weeks, 46), (> 1 Week, 2).

How can I create this chart?

 

[lbass]

Try a record selection formula like:

{table.date} in currentdate - dayofweek(currentdate)-20 to currentdate-dayofweek(currentdate)

Then insert a chart->advanced and choose {table.date} for your "on change of" field. Highlight {table.date} and choose "Order"->Chart values shown for each week. Use count of {table.ID} as your summary.

For the second problem, create a formula {@diff}:

if datediff("d",{table.date},currentdate) > 21
then "> 3 Weeks" else
if datediff("d",{table.date},currentdate) > 14
then "> 2 Weeks" else
datediff("d",{table.date},currentdate) > 7
then "> 1 Week" else
"<= 1 Week"

Use this as the "on change of" field and use count of {table.id} as the summary.

-LB

 

[mychal]

Thanks for the suggestions, lbass. I'm in the ball park, now, but I'm not on base yet. I created the formula you suggested and the chart you suggested, but my data points are not working yet.

Right now, my first data point is showing a count of records > 1 week, but less than 2 weeks. And the second data point is showing records > 2 weeks, but less than 3 weeks. And so on...

Instead, I want the first data point to show records > 1 week (this number should be higher than the 2nd data point). I want the second data point to show records > 2 weeks (this number should be lower than the first data point, but higher than the third data point). Etc.

I'm using CR 8.5, and though your instructions are not explicit, I extrapolated that in the Chart Expert, on the Data tab, you wanted me to use the @diff formula for the "On change of" and the count of ids as the "Show values" item. Perhaps I made a mistake here somewhere?

I'm thinking not, though, as my gut tells me that the @diff formula is the problem. It would seem to me that each record would only fit into one category ("> 1 Week", "> 2 Weeks"). Rather, I want a record where the datediff is 5 weeks to be counted in all three categories ("> 1 Week", "> 2 Weeks", "> 3 Weeks"). Am I on to something here, or am I being a Friday afternoon goofball?

 

[lbass]

My approach was incorrect. Try creating three formulas:

//{@>3 Weeks Ago}:
if {table.date} < currentdate - 20 then 1

//{@>2 Weeks Ago}:
if {table.date} < currentdate - 13 then 1

//{@>1 Week Ago}:
if {table.date} < currentdate - 6 then 1

Insert the chart->advanced. Instead of using "On change of" open the drop-down and choose "For All Records". Then use "sum of {@1 Week Ago}", "sum of {@2 Weeks Ago}", and "sum of {@3 Weeks Ago}" in the "show value" section.

-LB