Help with Select Formula

3.54K viewsFormulas and Functions

I’m having trouble getting the correct formula. I’ve attached a stripped down version of my model.

The problem formula is for the Open column in the Inventory Aging matrix.
I’ve included notes in the matrix comments to explain what is needed.

Any help is appreciated.



I’m thinking the same thing, that because matched item Ids could be in different Year items this is a problem.
I also wonder about the fact that the select is being done in a third matrix so that all aspects of the select reference other matrices.

Although what I want seems to be fairly straight forward

For each Sold Item Id, select Buy Items with matching Ids and return the Buy Item Ids and count them for each Year.
The tricky part is that I want the count to be placed in the Year item that cooresponds with the matched Buy item, not the Sell item.

counta(select (Buy Data::Item Id, // Return the matched Buy item ids and count them for each year.
Buy Data::Item Id, // Match ids with Buy items
Sell Data::Item Id)) // Start with existing sell items

It’s almost like the select doesn’t know which Year item to associate the matched items with because they sometimes are in different year items.



Thanks, Lyle.
I happened to try a similar attempt in front of my students yesterday: I had a formula in a model doing an extraction/aggregation from two matrices, with the help of an intermediate calculation. I told them “Look, we can also do the same thing with a direct formula”, wrote one and, (laugh), got a #SIZE error.
In your case, one problem was that the year data of open items did not necessarily match the year of the closed items. Maybe with some analysis and use of silent() functions you could have done, but I found that such intricate solutions add complexity and fragility to a model, which may challenge the modeler himself from the day after.


Hi Luca,

Thanks for your suggestion.
That certianly gives the correct answers. And after the hours I’ve wasted trying to solve this problem within a formula I’ll use it.

It seems to me, though, there must be a formula that gives the correct answers without needing an intermediate data result in a matrix. It’s almost like there’s some “directionality” to the select function and I’m going against the grain with this problem.

Maybe someone else can figure out this one.



Hi lyledp.
Please look at a possible solution in the enclosed model.
I created a column “date sold” in the “Buy data” matrix in order to do a more simple and exhaustive search by item id in the “Sell data” matrix.
Hope this helps.

Latest Questions