4 Replies Latest reply: Sep 1, 2011 9:12 AM by Jason Lee RSS

    Prior Year Selection in Bar Graph

      i've created a bar chart with period along the x-axis showing current year revenue.

      i've used set analysis so that the chart shows all period.

      i've used an if statement to get prior year period to data into the graph, but its only showing one period at a time.

      is there a way of getting prior year data on the same chart as current year data.

      thanks

        • Re: Prior Year Selection in Bar Graph
          Steve Baldwin

          Hi, If i understand you correctly you want to be able to select a single year then get that years figures and the one before, in this case as the 2 measures you could use:

          =sum({<Year = {$(=only(Year))},Period=>} value)

          =sum({<Year = {$(=only(Year)-1)},Period=>} value)

           

          Thanks

          Steve

          =sum({<Year = {$(=only(Year))},Period=>} value)

           

          Thanks

            • Prior Year Selection in Bar Graph

              Hi Steve,

              the issue is to do with an IF statement i have put into the expression..

              this is as follows:

              i have 3 categories for VersionSelect, they are Budget Forecast and Last_Year.

              When i select either Budget or Forecast, the first part of the IF formula works and the graph maps out the data by period for all periods.

              when i select Last_Year,this should get the second part of the IF statement to work, but it only shows data for one of the periods and not all the periods even though i've put Period = {"*"} into the set analysis.

              i woudl be very grateful for any other suggestions!

              many thanks

               

               

              =if(VersionSelect <> 'Last_Year',

              Sum({$<Input_Sheet = {"DM"},

                  Obj_# = {"*"},

                  KPI = {"Total print net advertising revenue (£m)"},

                  KPI_Definition = {"*"},

                  Owner = {"*"},

                  BU = {"DM"},

                  Level = {"*"},

                  Data_Contact = {"*"},

                  Data_Source = {"*"},

                  Period = {"*"},

                  YTD = {"YTD"},

                  CurPd = {"*"}>} $(vComp_Version)),

              Sum({$<Input_Sheet = {"DM"},

                  Obj_# = {"*"},

                  KPI = {"Total print net advertising revenue (£m)"},

                  KPI_Definition = {"*"},

                  Owner = {"*"},

                  BU = {"DM"},

                  Level = {"*"},

                  Data_Contact = {"*"},

                  Data_Source = {"*"},

                  Year = {$(#=Year-1)},

                  Period = {"*"},

                  YTD = {"YTD"},

                  CurPd = {"*"}>} Actual))

              /

              sum({$<Period = {"*"} ,

                  YTD = {"YTD"},

                  CurPd = {"*"}>} SumWeeks)