1 Reply Latest reply on Aug 13, 2018 2:24 AM by Naveen B

    Reference Line based on selected period from parameters

    M N

      Hi all,

       

      It's so frustrating for me and I've been spending along day to get this problem resolved. I have a bar chart with X axis is period (month-year) and Y axis is the cost. I'd like to create a reference line which can show the average cost for selected period from two parameters (Start Date and End Date), and it will dynamically change following the values from parameters.

       

      The pic below is the reference line I have now but it's not connected to the parameter yet.

       

       

      Also, I've already created a calculated field which compares the values from Start Date and End Date with "Date" field in database, but got confused what would be the next step.

       

      (IF ATTR([Date]) >= [Start Date] AND ATTR([Date]) <= [End Date]

      THEN [Cost] END)

       

      So sorry I couldn't upload any workbook since it's connected to company's datalake. Any help is appreciated. Thanks so much!

        • 1. Re: Reference Line based on selected period from parameters
          Naveen B

          Hi MN,

           

          1) Drag the calculated field to Detail Marks section

          2) Click on Y-Axis --> Add reference Line --> Select the field and select the aggregation average like below

           

          you will get the reference , one question why you are having 3 lines is there any dimension on detail which is separating the line graph then right the calculation like below

           

          {SUM(

          (IF ([Date]) >= [Start Date] AND ([Date]) <= [End Date]

          THEN [Cost] END)

          )

          }

           

          so that above calculation will run on your calc field

           

          If it helps kindly mark this answer as helpful or correct so that it will help others

           

          If it didnt help kindly attach sample workbook with superstore data so that it will be easy to decipher

           

          BR,

          NB