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

# 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?

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

I'm afraid I'm not understanding. I am particularly unclear about why you need to build a chart based on two dimensions when you don't want to build a chart based on two dimensions. Perhaps a sample QVW would help?

The one thing that I can see, though, is that you don't need if() in either expression. Including the condition in the set analysis itself should be more efficient:

sum({<Year=,[Rep Event]*={'Actuals'}>} Sales)
sum({<Year={'2010'},Month={'12'},View={'YTD'},[Rep Event]*={'Budget'}>} Sales)

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

Hi John,

you are right, I do not need 2 dimensions and with your help, it works fine now.

Thanks a lot!