Chart Date Limits with alternate states

[farnsworth]

Hi All,

I have chart which displays the results for a selection (this is in alternate State1) against the baseline control limits (inhertied state), so this is 3 lines on the chart which represent the upper, lower and control limit.

The chart works but issue I have is the positioning of the chart view when a selection is made.  So i have 2014 and 2015 in the calendar and if I select 1st to 10th of october 2015 the data filters correctly for the selection but the upper, lower and control limits still display across the entire date range.

Here is the calculation for the Control limit,  the upper and lower control limits are deviations from the control limit.

Avg(TOTAL Aggr(  sum([Call Answered])/ Count(DISTINCT [PersonID]) ,Date))

If I export the chart to excel i see the entire date range in the calendar has been populated for the control limits, as per below screenshot.

Ideally the chart should only show the 10 days selected.

Thanks in advance for any suggestions.

[Anonymous]

On the Presentation tab of your Chart Properties are both of these ticked ?

• Suppress Zero-Values
• Suppress Missing

And on the Dimension tab for all Dimension is this unticked ?

•      Show All Values

[farnsworth]

Hi, thanks for the response.

I have those selections ticked / unticked but if feel the issue is more around the set analysis side.

If I change the chart to a table the data looks as follows:

Date	Upper CL	Lower CL	CL	Call Ave	Deviation
Apr	27.9	15.9	21.9		2.0
May	27.9	15.9	21.9		2.0
Jun	27.9	15.9	21.9		2.0
Jul	27.9	15.9	21.9		2.0
Aug	27.9	15.9	21.9	29.3	2.0
Sep	27.9	15.9	21.9	24.1	2.0
Oct	27.9	15.9	21.9	29.0	2.0
Nov	27.9	15.9	21.9	24.6	2.0

So i don't want the Apr-Jul limits filled in.  I've tried to fix this using the set analysis with the following for the control limit as everything else comes from this.  This is what I have so far which isn't working.

Avg(TOTAL Aggr(sum({State1 < Date = {">= min(Date)"}>}[Call Answered])

  / Count({State1 < Date = {">= min(Date)"}>}DISTINCT [Agent RACFID])

,Date))

The control limit is in the inherited state and the selection which is being compared to the control limit is in state1.

[farnsworth · Accepted solution]

I managed to solve this and wish i'd thought of this method sooner.  I created a variable for the Control Limit and deviation and the upper and lower control limits.  Then in the chart I kept just the actual line.  Then in the presentation tab -> Reference Lines I added in the Control Limit and Upper and Lower control limits as three separate lines.  This way the chart is only as big as the actuals which is what I was after. 

This way does seem more flexible if you are happy with the control limits being held as variables.