Showing yesterdays data on a YTD report

[redbunny78]

I have a YTD report that shows has several formulas but I need to show the same data results for yesterday and the last 3 days. What is the best way to do so?

The output would look like:

Current YTD % 96%
2007 YTD / Success Rate 82,150 / 92.4
Yesterday / Success Rate 76% / 42
Last 3 Days / Success Rate 82% / 93

Thanks!

 

[lbass]

I don't know what I'm looking at here. It looks like you have both numbers and percentages in the column to the left of the "/", and I'm not sure what the figure is on the right. Please show the content of the formulas you are using (and the content of any nested formulas) and explain whether these results are in a group or report footer.

-LB

 

[redbunny78]

Sorry, I guess that was confusing...

The YTD formulas actually aren't the same as the ones I need for yesterday and last 3 days.

So I have a report with a few YTD formulas (the YTD is in the record selection formula) and need to show yesterdays results for the following formulas:

Target_Total:
DistinctCount of ADS.JOB_NAME

Complete%
100-(({@Outstanding}/DistinctCount ({ADS_JOB_ENDPOINT.ENDPOINT}, {@DateJobAdj}, "daily"))*100)

Outstanding
DistinctCount ({ADS_JOB_ENDPOINT.ENDPOINT}, {@DateJobAdj}, "daily")-DistinctCount ({DT_ATTEMPTS.ENDPOINT}, {@DateJobAdj}, "daily")

The results are in the report header. I hope this is enough information.

 

[lbass]

You must have a group on {@DateJobAdj} since you use it as a group condition in your formulas. Since you want to display this in the report header, you cannot use a group condition in your formulas.

What is the content of {@DateJobAdj}?

-LB

 

[redbunny78]

It is a formula for UTC time conversion.

DateTime((ToNumber(Left({ADS_JOB.JOB_DATETIME},4)))
,
(ToNumber(Mid({ADS_JOB.JOB_DATETIME},5,2)))
,
(ToNumber(Mid({ADS_JOB.JOB_DATETIME},7,2)))
,
(ToNumber(Mid({ADS_JOB.JOB_DATETIME},9,2)))
,
(ToNumber(Mid({ADS_JOB.JOB_DATETIME},11,2)))
,00)

The grouping was actually from another report that has the same formulas I'm looking for. That report showed last 7 days so it was DatejobAdj - D.

 

[lbass]

I don't know how your formulas relate to your first post, which really only shows two columns. But anyway, create a formula {@Null} by opening and saving a new formula without entering anything. Then create conditional formulas like this:

//{@yesterdayendpt}:
if {table.date} = currentdate-1 then
{ADS_JOB_ENDPOINT.ENDPOINT} else
{@Null}

Repeat for {DT_ATTEMPTS.ENDPOINT} and {ADS.JOB_NAME}. Then insert distinctcounts on these conditional formulas or use the conditional formulas instead of the fields in your formulas.

Note that you will have wrap {@Null} in date() or in tonumber() if the alternative result field in the formula is a date or number, respectively. If they are strings, no additional function is necessary.

For the last three days before today, use:
if {table.date} in currentdate-3 to currentdate-1 then...

-LB