how to do the equivalent of select function without size mismatches

4.64K viewsTips and Techniques

Hi –

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.

Any ideas?




Hi – you could approach this situation by building a key matrix to make sure each value item has an associated key. Please see the attached file.



Hi Mike,

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.


Latest Questions

Qloud Losing Formatting 4 Answers | 0 Votes
Meditation on timelines 3 Answers | 0 Votes