Need MTD and previous day totals 1

[Malekish]

Reaching out to this group for suggestions again.

Sample of data

Date Target_Calls Total_Calls
3/1/09 100 110
3/2/09 200 230
3/3/09 225 250
...
3/21/09 115 125

Here's what I'm trying to accompish.

MTD score which is defined as sum(Target_Calls) / sum(Total_Calls) (over appropriate time frame of @StartDate to @EndDate)

Previous Day Score = Target_Calls / Total Calls where date = datediff("d",-1,{@EndDate})

I have a whole series of reports I'm trying to maintain and expand that solved the problem by building a whole big ugly mess of arrays from {@StartDate} to {@EndDate}

Target_Calls[Counter] := etc.


Is this the best way of solving the problem? Is there a better way of calculating the previous day's score without all these arrays?

One example of my report builds these arrays, one for each location in their own subreport, and then passes the values to the main report. The main report shows the combined score of all 15 locations as well as the combined score of the previous day.

Don't you love inheriting old reports?

 

[dgillz]

datediff("d",-1,CurrentDate) will give you yesterday. Using {@enddate} will only work if {@enddate} happens to also be today.

I don't see the need for an array. Create 2 formulas:

If {YourDateField} = Datediff("d",-1,CurrentDate} then {YourAmountField} else 0

and

If {YourDateField} in MonthToDate then {YourAmountField} else 0

The group your report by day, location, etc.




Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[Malekish]

{@StartDate} and {@EndDate} are usually, but not always, defined as the first to the month to yesterday but there's enough exceptions that I allow the user to enter their own range.

If, for example, it was run under normal conditions we would see March 1 - March 29, have the MTD values as well as the values for only March 28th. I guess "MTD" may be confusing because it's not always the month. It may be March 15 - March 31, whatever.

I guess I'm confused by what you mean {YourAmountField}, you're losing me there. I'm still learning the internal logic of CR.

I can easily compute the Score for the date range by using sum(Target_Calls) / sum(Total_Calls) (or using a running total, etc).

How exactly would I compute the previous day's score where Previous Day is defined as datediff("d",-1,{@EndDate})

End result:

"Speed Dial" type graphs

Previous Day's Score MTD Score (defined as @Start to @End)

Thanks for your help!

 

[dgillz]

{YourAmountField} is equivalent to the database field that you are trying to sum. I don't know the name of your database field so I used a generic {YourAmountField}.

Also, why are {@StartDate} and {@EndDate} formulas instead of parameters?

I would dump both of those formula fields and create one DateRange parameter. Then your formula for the report becomes:

If {YourDateField} in {?DateRange} then {YourAmountField} else 0

Sum this field as needed.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[Malekish]

Heh, they're formulas because I wrote them out as {@Start} instead of {?Start}. Yes, they really are parameters I just had the wrong syntax while typing these posts.


You can create a date-range parameter? Really? How do you do that? I was always shown to use {?Start} to {?End} (I learned on the job, no formal training)

 

[Malekish]

Sorry for the shameless bump however I've been looking into how to create a daterange and I don't see an easy solution.

Is there something obvious I'm missing? CR 11

 

[dgillz]

Edit the parameter value and change the allow range values property to true.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html