1 Reply Latest reply on Dec 3, 2012 8:14 AM by Jonathan Drummey

    Use parameter & lookup to select different values for before & after selected parameter

    Hannah Moore

      Help please!

       

      What I would like to do is have a parameter where the user can select a month & year.  Based on that selection, what would display in the viz would be different for the prior, current and future fiscal years (Fiscal Year starts October 1.)  

      • For fiscal years years prior to the year selected, I would like to display only the last actual value of that year. (value for Max date partitioned by fiscal year)
      • For the selected fiscal year, display the actual and target for the month selected.  (value for selected date)
      • For future fiscal years, display the first plan value of that fiscal year (value for Min date partitioned by fiscal year)

       

      Example:user selects October 2012 (which is FY2013). 
      Viz would display:

      • FY2012 Actual from 9/1/2012 (max date from FY2012): 712.0
      • FY2013 Actual from 10/1/2012 (selected month & year): 724.0
        • Also displays Target from 10/1/2012  (selected month & year): 760.0
      • FY2014 Plan from 10/1/2013 (min date from FY2014): 730.0
      • FY2015 Plan from 10/1/2014 (min date from FY2015): 741.0
      • FY2016 Plan from 10/1/2015(min date from FY2016) 743.0

       

      I think there should be a way to do this using the Index & Lookup table calcs, but I can't figure out how to make it work. 

      The data source is in excel and just getting set up - so I can reconfigure how it looks if there is a better way to make this work efficiently

       

      My data set currently looks like this (not full set, but hopefully illustrative):

       

      TypeDate Value
      Plan10/1/2011 714.0
      Plan11/1/2011 714.0
      Plan12/1/2011 714.0
      Plan1/1/2012 714.0
      Plan2/1/2012 714.0
      Plan3/1/2012 714.0
      Plan4/1/2012 714.0
      Plan5/1/2012 714.0
      Plan6/1/2012 714.0
      Plan7/1/2012 714.0
      Plan8/1/2012 714.0
      Plan9/1/2012 714.0
      Plan10/1/2012 719.0
      Plan11/1/2012 719.0
      Plan12/1/2012 719.0
      Plan1/1/2013 719.0
      Plan2/1/2013 719.0
      Plan3/1/2013 719.0
      Plan4/1/2013 719.0
      Plan5/1/2013 719.0
      Plan6/1/2013 719.0
      Plan7/1/2013 719.0
      Plan8/1/2013 719.0
      Plan9/1/2013 719.0
      Plan10/1/2013 730.0
      Plan11/1/2013 730.0
      Plan12/1/2013 730.0
      Plan10/1/2014 741.0
      Plan11/1/2014 741.0
      Plan12/1/2014 741.0
      Plan1/1/2015 741.0
      Plan10/1/2015 743.0
      Plan11/1/2015 743.0
      Plan12/1/2015 743.0
      Plan1/1/2016 743.0
      Plan2/1/2016 743.0
      Plan3/1/2016 743.0
      Target10/1/2011 713.6
      Target11/1/2011 713.6
      Target12/1/2011 713.6
      Target1/1/2012 713.6
      Target2/1/2012 713.6
      Target3/1/2012 713.6
      Target4/1/2012 713.6
      Target5/1/2012 713.6
      Target6/1/2012 713.6
      Target7/1/2012 713.6
      Target8/1/2012 713.6
      Target9/1/2012 713.6
      Target10/1/2012 760.0
      Target11/1/2012 760.0
      Target12/1/2012 708.2
      Target1/1/2013 708.2
      Target2/1/2013 708.2
      Target3/1/2013 708.2
      Target4/1/2013 708.2
      Target5/1/2013 708.2
      Target6/1/2013 708.2
      Target7/1/2013 708.2
      Target8/1/2013 708.2
      Target9/1/2013 708.2
      Actual10/1/2011 701.0
      Actual11/1/2011 702.0
      Actual12/1/2011 703.0
      Actual1/1/2012 704.0
      Actual2/1/2012 705.0
      Actual3/1/2012 706.0
      Actual4/1/2012 707.0
      Actual5/1/2012 708.0
      Actual6/1/2012 709.0
      Actual7/1/2012 710.0
      Actual8/1/2012 711.0
      Actual9/1/2012 712.0
      Actual10/1/2010 706.6
      Actual10/1/2012 724.0

       

      Any help would be appreciated!!!

       

      Hannah