How to retrieve event records from a table, if their weeknumber(s) correspond to the current row weeknumber?

[Xavier_Rabilloud]

Hello,

I have a table in which I input communications opportunities (external events). These events can correspond to a single day or to a date range, which can span over several weeks.
I have been able to extract the weeknumbers corresponding to the date range defined by the start and end dates :

Then, in another table where I map our upcoming newsletters (each newsletter in a column), I also have created a column for these communications opportunities. I want it to show (only) the opportunities which happen (partially or totally) during the week corresponding to the calendar line (which is structured along the principle : 1 week = 1 row, with all weeks of the year in sequential order).

But my formula gets me ALL records of the opportunities table in EACH cell where I should get only one event.

I fail to find what’s wrong in my formula or structure…

Do you see what I miss in that ?

Many thanks for any help !

Best

[Federico.Stefanato]

Hi @Xavier_Rabilloud,
from what I see, your formula is correct, so I’m not able to spot anything strange.
However, it would help to have a look at the document (or a workable copy of it) to see if there is something affecting its behaviour.

Let me know.

P.S.
Not relevant to your question, but perhaps it worths pointing out that you could simplify your formula using WithName(): this way you can avoid the filter redundancy.
Something like:

[Your Table].Filter(...).WithName(data,
  If(data.isNotBlank(), data, "")
)

[Xavier_Rabilloud]

Hi @Federico.Stefanato

Thanks for taking a peek into this !

I’am afraid I don’t really know the proper way to share my doc itself : when I type your name in the “invite” field of the “Share” pop-up, Coda tells me “no contacts found”.

How may I share the doc with you ?

[Federico.Stefanato]

Hi @Xavier_Rabilloud,

if you want to have explicit filtered people, you can embed your doc (Share, then Embed) here and I’ll ask for sharing permissions.

[Xavier_Rabilloud]

hi @Federico.Stefanato here it is.

[Xavier_Rabilloud]

For reference by others, here are the insights brought to me by @Federico.Stefanato

« Basically, your filter had a subtle - but crucial - difference: you were not filtering by the column of the filtered table but of a list of columns taken from the same table… And it was true for any list.
It’s hard to explain, but if you look at my example perhaps it clarifies. »

Here is the correct formula :

There after, using this formula to correct another formula, I actually realized that the two below do not get you the same thing, and only the 2nd form works correctly. While I thought the 1st form was only more explicit thus more rigorous, but not at all, it does something else, which I still have to understand precisely :

1st form (actually not working) :

2nd form (correctly working) :