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.
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.
Yes, I now realize this. I marked Juliet as Helpful Answer. Thanks to both.
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.
6. Add labels
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]
Thanks for the entertainment.
median average ML JMcC_jimw.twbx 827.3 KB
I LOVE this type of thread--I learn so much by seeing multiple solutions to the same problem. THANK YOU!
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.
Thanks for following that up Jim. I was really hoping someone would finish it off.
For me there are loads of applications for this so it's been a really helpful thread.
Many thanks and best wishes.
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.
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.
median average ML JMcC_jimw.twbx 840.7 KB
Thanks v much.