--Help With Running Totals--

[goduke]

I have a report that looks at the last 14 days worth of data. I have to use running totals to get my totals for each day but I am having some issues doing so. I have data from a ticketing system and I have to count how many tickets have been opened for 1 Week, 2 Weeks, 3 Weeks, 4 Weeks, 30-60 Days, 60 or more Days, for each of the 14 days. It is based on the open date of the ticket and the date of one of the 14 days. Here is an example of how the report should be laid out(except for 14 days and not 4):

Day4 Day3 Day2 Day1
Aged 1 Week 1 1 2 3
Aged 2 Weeks 2 2 1 1
Aged 3 Weeks 2 2 2 2
Aged 4 Weeks 1 1 1 1
Aged 30-60 Days 1 1 1 1
Aged 60 or more Days 1 1 1 1

I have tried a few different things but I cannot get anything to work. Please help!!

 

[lbass]

I can't follow how the 14 most recent dates relate to the aging categories, particularly the ones with ranges. Let's say there is a ticket date 35 days out, how would you know which of the 14 dates to count the ticket?

-LB

 

[hilfy]

This is going to be tough and you may want to use a Stored Procedure or Command to get the database to do the heavy lifting instead of doing it this way. Either you need to be familiar with SQL or you need access to someone who is in order to be able to go this route. I have some thoughts on how to do this if you're interested.

If you're going to do all of this in the report, here's what you'll need to do as I see it (I'm sure someone will correct me if I'm wrong!)

1. Create a formula for each of the 14 days to determine what the date is for that date. For this example I'll call them {@Day1}, {@Day2}, etc. How these formulas will look depends on what Day 1 is - if it's today, then {@Day1} will be CurrentDate, {@Day2} will be CurrentDate - 1, etc. If Day 1 is yesterday then you'll start with {@Day1} being CurrentDate - 1.

2. Create another set of formulas to determine the age of a record on any given date. I'll call this {@Day1Age} and it will look like:

{@Day1} - {table.date field} + 1

3. Create a formula to determine the age of a record on each day. This might look something like this:

Switch(
  {@Day1Age} >= 60, 60,
  {@Day1Age} in 30 to 60, 30,
  {@Day1Age} in 28 to 29, 4,
  {@Day1Age} in 22 to 27, 3',
  {@Day1Age} in 15 to 21, 2',
  {@Day1Age} in 8 to 14, 1',
  true, 0)

You could also do this by putting the Date and Age calculations from steps 1 and 2 directly in your Switch statement, but I like doing it this way for two reason - it's more readable and, if you have to make any changes to a calculation you only have to do it in one place.

5. Create a Running total for each combination of day and age (84 of them!). I'll call this {#Day1Week1Count} and it looks like this:
Field to summarize: Some unique field that identifies what you're counting.
Type of summary: Count
Evaluate - Use a Formula: @Day1Age = 1
Reset: Never if you're only showing this once on a report, On Change of Group if you're showing it for each item in a specific group.

6. Put your data in either a group footer or the report footer - with running totals it has to be at the END of the data you're totalling.

I hope this helps!

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.