Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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)
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)
Hi John,
you are right, I do not need 2 dimensions and with your help, it works fine now.
Thanks a lot!
Adi