I have a situation where I have top 20% ranked full year data and top 20% ranked YTD data for the same item (let’s say it’s revenue) in different matrices each backed by their own datasource-database via dataNAV. I want to build a view that shows three different years (2010, 2009, 2008) with full year data and YTD data alternating for each year in descending order ala:
‘2010’ ‘2010 YTD’ ‘2009’ ‘2009 YTD’ ‘2008’ ‘2008 YTD’
Normally I use the select function to do something like this, but because the number of rows varies between full year and YTD values I get #SIZE errors when trying to do so. Is there some way around this? Some way of saying “populate 2010 column in matrix c with values that correspond from matrix a; populate 2010 YTD column in matrix c with values that correspond from matrix b; and don’t complain about differences in #SIZE for either of them”. It would act like an if statement that parses the category value in matrix c and looks up the appropriate value in the column requested for that category value in matrix a or b (as requested) and doesn’t need to understand if both dimensions have the same size.
In the example above I have matrix a and b working fine, it is just combining them into another matrix that is difficult.
Nice model, thanks. I have updated the model though to reflect the problem more accurately. Please see attached and note the dimension problem. The change I made reflects the situation whereby the number of rows in full year data and ytd data is not the same thus producing the #SIZE error.
Thanks for your help,
Hi – Thanks for the post.
I attached a model with 2 approaches. One involves adding a new dimension with the items of “full year” and “YTD” and writing a select function for each…
The other approach involves utilizing a SWITCH function to detect if you are in a YTD item or not, then using the appropriate SELECT statement.
Hopefully one of these approaches moves you forward. Feel free to post again if you have further questions.