5 Replies Latest reply: Oct 18, 2013 6:38 AM by carl carl RSS

    Dynamic Target Line for Bar Chart based on a Bookmark

      Hello All,

       

      I am trying to create a target line for my bar chart based on year 2012 to compare against data prior and post 2012. I have created the bookmark BM2012 to help. What I am expecting from my formula is that the target line remains the same for any given set of listbox selections where the only difference is the year selected. 

       

      To illustrate, my intention is that:


      The target line should stay the same for these two selections:


      Year:2011-12
      Department: BBB
      Section: 123
      Customer: AAA


      Year:2012-13
      Department: BBB
      Section: 123
      Customer: AAA


      But likely be different for the following:


      Year:2011-12
      Department: BBB
      Section: 123
      Customer: AAA


      Year:2011-12
      Department: BBB
      Section: 123
      Customer: CCC

       


      The code I am using is the same as the bar chart and is:

       

      =sum(aggr(sum({BM2012}{$<[L O S] = {">= $(vSlider)"}>}Distinct [L O S]), ZNumber)) / count({BM2012}{$<[L O S] = {">= $(vSlider)"}>} Distinct ZNumber )

       

       

      Thanks in advance