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
        Luis Amadeo

        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.


        Luis Amadeo

        • 16. Re: Change the median line
          Matt Lutton

          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
            Luis Amadeo

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

            • 18. Re: Change the median line
              Jim Wahl

              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):

              2013-12-19 17-59-47.png


              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]

              ELSE NULL




              Thanks for the entertainment.



              • 19. Re: Change the median line
                Matt Lutton

                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
                  Jim Wahl

                  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.


                  2013-12-19 18-20-14.png

                  • 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
                      Matt Lutton

                      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.



                      • 23. Re: Change the median line

                        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.



                        • 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



                          • 25. Re: Re: Change the median line
                            Jim Wahl

                            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.


                            2013-12-20 12-25-40.png



                            • 26. Re: Re: Change the median line

                              Brilliant.  Sorted.

                              Thanks v much.



                              • 27. Re: Change the median line
                                Jim Wahl

                                No problem.

                                1 2 Previous Next