11 Replies Latest reply: Aug 3, 2017 6:49 PM by Rob Wunderlich RSS

    Median value on x-axis

    Steve Holmes

      I have a bar chart (histogram, really) where the x-axis is a rounded number of hours (the delay in hours between admission to hospital and the start of surgery), and the y-axis is the number of cases.

       

      Is there a way to show a vertical line at the median number of hours?

       

      RoundedOpDelay is a dimension, calculated on data load.  However, I also have a separate numeric field called Delay(Hrs) to two decimal places if that works better.

       

      Thanks,

       

      Steve

        • Re: Median value on x-axis
          Tim Driller

          you can try this Expression

           

          median(TOTAL [Delay(Hrs)])

            • Re: Median value on x-axis
              Steve Holmes

              Thanks, Tim,

              I have a KPI box that shows the median value as a number using your formula, and maybe I should be satisfied with that.  It would be really impressive, though, to have a different-coloured vertical line on the chart.

              To chart this as a second dimension I would need something to use as a Y-axis number, probably matching the maximum number of operations per RoundedOpDelay (or the chart range).

              Any suggestions?

              Thanks again,

              Steve

                • Re: Median value on x-axis
                  Tim Driller

                  what about adding this expression median(TOTAL [Delay(Hrs)]and display as line

                  the screenshot is only an example

                  regards

                  tim

                    • Re: Median value on x-axis
                      Steve Holmes

                      Thanks, Tim,

                       

                      If you were to group your Total Sales into, say, $500 blocks and chart them on the X-axis, you would be able to chart the number of times you hit each group of $500.  Looking at your example, there would be a value of 1 for <$500, 6 for $1000, 3 for $1500, 4 for $2000, 0 for $2500, 2 for $3000, 0 for $3500 and 1 for $4000.  (Be patient here; I'm sitting with a ruler pressed on the screen and people are laughing at me.)

                       

                      Your data doesn't readily lend itself to my histogram, but this is the sort of thing I'm hoping for:

                      The columns represent the $500 groups; the value is a count of order dates, and the vertical red line represents the median order value.

                       

                      On my bar chart at the top, the vertical red line would probably turn up at about 17 hours, as there's a long tail out to the right.

                       

                      Thanks for your interest!

                       

                      Steve

                • Re: Median value on x-axis
                  Rob Wunderlich

                  The Reference Line option for Bar Chart should be available under Add-ons, Reference Lines.

                   

                  -Rob

                  http://masterssummit.com

                  http://qlikviewcookbook.com

                    • Re: Median value on x-axis
                      Steve Holmes

                      Thanks Rob,

                      I can add the median value as a reference line, but it plots against the Y-axis as a horizontal line, rather than on the x axis, vertically.

                      Thanks for your help!

                       

                      Steve

                        • Re: Median value on x-axis
                          Rob Wunderlich

                          Hadn't noticed that there is no X/Y option for the ref line (unlike that other product). What a disappointment.

                           

                          You could color the bar closest to the median as a sort of workaround.

                           

                          -Rob

                            • Re: Median value on x-axis
                              Steve Holmes

                              Hi,

                              In case anybody is looking for this answer - seems unlikely - here's what I did:

                               

                              I used color by expression, using this:

                               

                              I had hoped to find the RGB value for the standard colors but in the end I used a color picker and found the closest I could.

                               

                              Thanks for the suggestion, Rob.

                               

                              Steve

                                • Re: Median value on x-axis
                                  Rob Wunderlich

                                  To use a color from the standard palette, you can use the color(n) function. e.g. "color(2)" for the red color. These are slightly less saturated versions than the auto colors, but at least you get consistency.

                                   

                                  -Rob

                                  http://masterssummit.com

                                  http://qlikviewcookbook.com

                                    • Re: Median value on x-axis
                                      Steve Holmes

                                      Thanks, Rob,

                                       

                                      You'll have seen the peculiarities in June 2017's Colors / Custom / by Expression.  My original application had a map with the location of ambulance pick-up points by the type of injury event, where a car crash displayed a blue spot, motorcycle was cyan, fall was red, and so on for six trauma types.  It made a very interesting and informative map.  (This is a tiny illustration.)

                                      All of the spots became grey with June 2017's release.

                                       

                                      I found a post that explained that colours had to be enclosed in speech marks ('), and I've alternated these depending on the effect.

                                       

                                      In case other people have this problem, your color(2) works without speech marks. Red() - a rich, deep colour - works without, but red (a dramatic crimson) needs them: 'red'.

                                      In my map, both RGB and ARGB must be enclosed

                                      but in a plain bar chart they don't:

                                      I understand that this is a bug that will be fixed in an update, but in the meantime a little experimentation helps!

                                       

                                      Steve

                                        • Re: Median value on x-axis
                                          Rob Wunderlich

                                          The map bug I'm not sure about. But I can explain the quote requirement on the bar chart.

                                           

                                          RBG() and red() are both functions (note the parens) that must be evaluated, so they are unquoted.

                                           

                                          'red' is a "color keyword" and it therefore quoted as literal string.

                                           

                                          Interesting how many flavors or red we have now

                                           

                                          -Rob