Set analysis modifier between two dates

[#1]

Hello folks,

Please help me. I'm going mad.

I'm trying to do something using set analysis that I think ought to be simple, but I just can't seem to get the syntax right.

In the attached file I have two tables: calendar and event. They are linked through the date field.

With Date as the dimension, I want to plot "Result" from the Event table as a dot, and "Open" from the Calendar table as a circle. So that every available day is shown on the chart, with a dot for every event that occurred.

I want to be able to select a month from Calendar and an Event type from events and see a circle for every day that month and a dot for every event.

I think I need something like

=avg(  {1 <Date={"<max(Date)>min(Date)"}  }  Open)

but I can't get it right. There needs to be a dollar or two in there and I can't figure out where!

I even messed with the P() function, but ended up confusing myself.

Any help greatly appreciated!

Karen

[sunny_talwar]

May be this

=Avg({1<Date = p(Date)>}Open)

[#3]

Thank you Sunny.

That's not quite what I need. It plots only the Dates in the current selection. I want ALL the Dates, within the max and min Dates of the current selection.

Does that make sense?

[sunny_talwar · Accepted solution]

May be this

=Avg({1<Date = {"$(='>=' & Min(Date) & '<=' & Max(Date))"}>}Open)

[#5]

Yes!!!! Thank you so much! I lost a whole day trying to do that!