Line Chart Stop Displaying Series after CurrentDate

[random621]

I have a line chart that tracks actual new clients (forms in) per day vs the monthly new client goals per day.

The first series (Target) works exactly how I want - as you can see in the linked file below the blue series (Target) extends though the end of the month. I get this data from a GoalTracking access table that has future goals for the next 3 years in it.

The second series (Actual) comes out of my CRM SQL database and it shows the actual forms in. In essence this series works, but I would really like the Actual line to cut off after the 15th of July (current date). This series is generated by a running total. I've tried suppressing the running total anywhere past the current date, but that does not help.

Any help is greatly appreciate - I suspect I shouldn't be using running totals or possibly a different type of chart but I'm lost at this point.

Ryan

 

[lbass]

Try charting on a formula:

if {table.date} <= currentdate then
{#runningtotal} else
tonumber({@null})

...where {@null} is a formula created by opening and saving a new formula without entering anything.

-LB

 

[random621]

Thanks LB.

I added 2 new formulas "Actual" and "Null".

Formula for Actual is:
If
{GoalTracking.Date} <= CurrentDate
then
{#Actual Forms Out}
else
tonumber({@Null})

Formula for Null is blank.

I have 1 group in my report GoalTracking.Date (by day). When I put @Actual in either details OR Group footer 1 and then go to my Chart Expert @Actual is NOT available under the list of "Available Fields". My chart is a standard Line chart (on the Data tab "Advanced" is selected).

Am I doing something wrong here?

 

[lbass]

If you are already charting on the running total {#Actual Forms Out}, there is no reason that {@Actual} would not be available, too.

How is the running total set up? Does it reference the date field already?

How is the chart set up and where is it located?

-LB

 

[random621]

The chart is in my report footer.

{#Actual forms Out} is a distinct count of Customer.Accountid that evaluates based on the following formula:

CurrentDate >= {GoalTracking.Date}
and
{SP_BROKERAGE_SALES.BROKER_ROLE} = "Sales"
and
Date({BROKERAGE_ACCOUNT.FORMOUT}) = Date({GoalTracking.Date})

{#Actual forms Out} does NOT Reset.

I've attached a link to the actual report file below.

Again - thanks for the help!

 

[lbass]

Obviously I can't run the report, but try creating this conditional formula {@Actual2}:

If {GoalTracking.Date} <= currentdate and
{SP_BROKERAGE_SALES.BROKER_ROLE} = "Sales" and
Date({BROKERAGE_ACCOUNT.FORMOUT}) = Date({GoalTracking.Date}) then
{BROKERAGE_ACCOUNT.ACCOUNTID} else
{@Null}

Use this for the actual line in the chart (remove the running total first) and insert a distinctcount on it.

-LB