Formula displays zero when placed in report header 3

[sdzlkds]

I have a report where I want to show a chart that displays the KPI target met yesterday, and the average KPI target met over the last 3 months

No problems with the 3 month average measure, but the formula to get yesterday's KPI works in the report footer, but not in the report header, where the chart is placed.

The formula I an using is

if {dsplit.row_date} = currentdate -1 then
100*(Sum({dsplit.acceptable},{dsplit.row_date}))/sum({dsplit.callsoffered},{dsplit.row_date})
else 0

is there any way round this?

Thanks

 

[hilfy]

The problem is that the sums are calculated as the report processes, so the data is not available yet in the report header. If you must have the chart in the header, then you'll have to put the chart into a subreport and place the subreport in the header. It will take your report longer to process as it will have to query your data twice, but it's then only way to get the format you're looking for.

-Dell

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

 

[IanWaterman]

its just luck that formula works in footer.

Formula is based upon a record value

if {dsplit.row_date} = currentdate -1 then 100*(Sum({dsplit.acceptable},{dsplit.row_date}))/sum({dsplit.callsoffered},{dsplit.row_date})else 0

In your footer the last record meets the condition

{dsplit.row_date} = currentdate -1

where your first record does not.

If you want it to work in Header you can restrict data in select expert using

{dsplit.row_date} = currentdate -1

Ian

 

[lbass]

If you can't use the selection formula as Ian suggests, you could create conditional formulas like this:

//{@yesterdayacceptable}:
if {dsplit.row_date} = currentdate -1 then
{dsplit.acceptable}

//{@yesterdaycallsoffered}:
if {dsplit.row_date} = currentdate -1 then
{dsplit.callsoffered}

Then the formula becomes:

sum({@yesterdayacceptable},{dsplit.row_date})%
sum({@yesterdaycallsoffered},{dsplit.row_date})

Your original formula said show me the percent for all records if the current record = yesterday. I think you meant, show me the percent for yesterday.

-LB

 

[sdzlkds]

This is now sorted - thanks all for you assitance