2 Replies Latest reply: Nov 26, 2010 4:07 AM by ASchempp RSS

    Including a static target value for current year in a yearly sales trend overview

      Hi Everybody,

       

      I am trying to do the following chart, where the purpose is to compare the same reporting month over several years (e.g. if the user selects July), he will see July 2007 vs. 2008 vs. 2009 vs. 2010 for Actuals. On top of that I would like to include the Year End Budget for the Current Year as a static value, so even if the period of comparison is changing, this value stays the same.

       

      The data is structured as follows:

       

      Country

      View

      Rep Event

      Year

      Year View

      Month

      Sales

      A

      MTD

      Actuals

      2007

      CY-3

      1

      100.00

      A

      MTD

      Actuals

      2007

      CY-3

      2

      120.00

      A

      YTD

      Actuals

      2007

      CY-3

      1

      100.00

      A

      YTD

      Actuals

      2007

      CY-3

      2

      220.00

      A

      MTD

      Budget

      2007

      CY-3

      1

      110.00

      A

      MTD

      Budget

      2007

      CY-3

      2

      130.00

      A

      YTD

      Budget

      2007

      CY-3

      1

      110.00

      A

      YTD

      Budget

      2007

      CY-3

      2

      240.00

      A

      MTD

      Actuals

      2008

      CY-2

      1

      80.00

      A

      MTD

      Actuals

      2008

      CY-2

      2

      100.00

      A

      YTD

      Actuals

      2008

      CY-2

      1

      80.00

      A

      YTD

      Actuals

      2008

      CY-2

      2

      180.00

      A

      MTD

      Budget

      2008

      CY-2

      1

      100.00

      A

      MTD

      Budget

      2008

      CY-2

      2

      120.00

      A

      YTD

      Budget

      2008

      CY-2

      1

      100.00

      A

      YTD

      Budget

      2008

      CY-2

      2

      230.00

       

      So the static value I would like to include for Budget is where View = YTD and Rep Event = Budget and Year View = CY and Month = 12.

       

      For the actual Sales I am using the following expression:

      SUM({$<Year = >}IF( [Rep Event]='Actuals', "Sales", 0) )

       

      For the budget Sales I am using the following expression:

      sum({$<Year = {2010}, Month = {12}, View = {'YTD'}>} IF( [Rep Event]='Budget', "Sales", 0))

       

      But if I do that, I need to build the chart based on 2 dimensions: Year and Reporting Event, which does not look very nice, as for the previous years Budget will always be empty.

       

      Is there a smarter way to do this?

      Thanks for your help!