3 Replies Latest reply on Aug 21, 2016 8:18 PM by Unnikrishna Nair

    Reference Lines Based on Filter Selections

      Hello friends -


      My x-axis displays a continuous value of work performed, linear feet evaluated.  I've got a date filter so the user can select, yesterday, last week, last month, last few days, whatever.  Ideally, the users being dashboarded would be able to evaluate X feet of service per work day, say 3000 feet.  So, what I'd love to do is push a reference line into the graph that indicates the desired breakpoint, but to get this there, I really need to take into consideration the date value selected by the user; i.e., if they select last week, my reference point would be at 15K, but if they selected yesterday, it would render down to 3K.  I understand that there is some support for parameters, but the fulcrum of this decision lays with the filter selection.


      I'm thinking this is probably not supported right now, but am open to interesting ideas and/or hacks!


      Any insight is appreciated.


        • 1. Re: Reference Lines Based on Filter Selections
          Jonathan Drummey

          Reference lines can be calculated fields, so that part is pretty straightforward. The challenge is how to figure out which value to use for the line. There are two ways I can think of, one would be to use a parameter instead of a quick filter to drive the date filter, and also use that parameter to set the reference line. The second method would be to write a table calculation to determine the date period used in the (quick-filtered) view and then use that to determine which reference line to draw.


          If you need more assistance, post a packaged workbook with some sample data and we'll see what we can do!

          • 2. Re: Reference Lines Based on Filter Selections
            Shawn Wallwork

            Assuming your reference line is based on a per day calculation I think a variation of the attached will do what you want. Here's what I did:



            One other note: when you use it to create the reference line you can aggregate it using maximum, minimum, average, or median, it really doesn't matter because all of these produce a single value, in this case the same value. The only aggregation you can NOT use is SUM, that will add up all the rows producing a sum of a total.


            With this result:




            Message was edited by: Shawn Wallwork

            1 of 1 people found this helpful
            • 3. Re: Reference Lines Based on Filter Selections
              Unnikrishna Nair



              I have a similar question. In my problem there are different levels like category, subcategory, product name etc.

              I have filters for each.

              Here the reference line should be the average value of the filter selections of category and subcategory and NOT Product Name. So except when a product is being selected the reference line and actual line should coincide..

              I tried excluding Product Name by using {exclude [Product Name]: SUM(PROFIT)} but it didn't work out well.

              Help much appreciated.


              I am also attaching the file to this post.