1 2 Previous Next 27 Replies Latest reply on Dec 20, 2013 2:49 AM by Jim Wahl Go to original post
• ###### 15. Re: Change the median line

Thank you Matthew and Juliet, actually, both approaches will work for me now that I have given it more thought. I will mark both answers correct.

• ###### 16. Re: Change the median line

The forum only allows one "correct" answer, but I don't care who it goes to.  The important part is you got the help you needed.

• ###### 17. Re: Change the median line

Yes, I now realize this. I marked Juliet as Helpful Answer. Thanks to both.

• ###### 18. Re: Change the median line

Hi Luis,

I think you may have a solution you're happy with already, but I thought this was kind of a fun five-minute break from real work.

I modified Julia's workbook and created this (Sheet 16):

The lines are Gantt bars with maximum size. (If you wanted a dotted line, you could reduce the size a bit.)

Here are the basic steps:

1. Create calculated fields for the Median First 15 Days and Last 15 Days

Since you want an aggregate of an aggregate MEDIAN(SUM()), you need to use a table calculation.

Median First 15 Days = WINDOW_MEDIAN(SUM(IIF(DAY(Date)<=15,[Number of Records],NULL)))

2. Create a calculated field for the display

Median Line = IIF(MIN(DAY(Date)) <=15, [Median First 15 Days], [Median Last 15 Days])

3. Create the chart

Drag Median Line to the right axis to create a dual-axis chart.

In the Marks shelf, choose Gantt Bar. Click the Size button and increase the size. Adjust the color, ..

4. Sync the axes

If you click on the right axis, you'll see that Synchronize Axis is grayed out. This is because the left axis, Number of Records, is an integer and the right axis is a float. I fixed this by created a new field Number of Records (fload) = FLOAT([Number of Records]). Drag this new field on top of the old pill and now you should be able to sync the axes.

5. Hide the right axis by clicking on the axis.

There may be a better way of doing this, but but I created a new calculated field:

Median Line Label:

IF FIRST()==0 THEN [Median Line]

ELSEIF LOOKUP([Median Line], -1) <> [Median Line] THEN [Median Line]

ELSE NULL

END

Thanks for the entertainment.

Jim

• ###### 19. Re: Change the median line

I LOVE this type of thread--I learn so much by seeing multiple solutions to the same problem.  THANK YOU!

• ###### 20. Re: Change the median line

Minor, but one other thing you should really do is move the horizontal lines to the back. Click on the right-axis > Move marks to back.

• ###### 21. Re: Change the median line

Thank you Matthew for correcting my inaccurate formula that would never work on a discrete date.  Forgive me for trying to help without testing anything first and posting false information for everyone to get so confused about.  Perhaps I should not post at all. - ever.  Heaven forbid I guess right someday.  I'll mind my own business and make sure never to post unless I have tested it and is 100% correct.

• ###### 22. Re: Change the median line

I apologize if that was taken as an insult--it was not intended to be.  Please, continue posting.

Note that it wouldn't work on a continuous date either.

Cheers.

• ###### 23. Re: Change the median line

Thanks for following that up Jim.  I was really hoping someone would finish it off.

Many thanks and best wishes.

Juliette

• ###### 24. Re: Change the median line

Hi Jim (and anyone else interested!)

Just duplicated what you've done in your workboook.  Can I just check that you can't think of any way to keep the colours on the lines for before and after 15 days?

Where I want to apply this (a patient coming in for appointments and then changing lead clinician/therapist/etc part way through treatment, or changing a treatment type or treatment intensity) it would be really helpful to keep the colour change but without the break in the line (which might suggest that there was a break in their treatment).

To achieve this using the Seattle 911 data, I had duplicated the data source and filtered to over 15 days.  I couldn't do this manually for each change in therapist/treatment/etc as they may be many changes.

Any ideas?

Best wishes

J

• ###### 25. Re: Re: Change the median line

Dimensions create panes, which create splits in lines.

By default a calc Days < 15 = IIF(DAY(Date) <= 15, "yes", "no")   --- will be a dimension. If you drag this to the Color button, you can convert the pill in the view to a measure by clicking the pill and selecting Measure > Min. Or you could drag Days < 15 to the Measures pane and set the default aggregation to minimum, and then drag this pill to the Colors button.

Jim

• ###### 26. Re: Re: Change the median line

Brilliant.  Sorted.

Thanks v much.

Juliette

• ###### 27. Re: Change the median line

No problem.

1 2 Previous Next