Count number of dates that happen before the given date

[#1]

I have a list of events and I want to be able to give each event an event number based on the date it occurs in the year, so if there are no dates before it in the year then it will be event 1 when the next year rolls around I'd want it to start over at 1 and begin numbering the events again.  Here is my initial expression for the calculated dimension:

=Count( DISTINCT { < [EventStartDate] = { '<=$(=Date([EventStartDate]))' } > } [EventStartDate])

I imagine that I'll have to put something in there for the year, but at this point I can't even get this simple expression to work.

Any ideas?  Rather than giving me the solution can someone try to explain to me what the syntax of the expression is so that I can build more in the future?

[Gysbert_Wassenaar · Accepted solution]

It's probably easiest to create such a field in the script using the autonumber function.

Temp:

Load

    EventID,

    EventStartDate,

    year(EventStartDate) as Year,

    ...other fields...

From ...somewhere...;

Result:

Load *, autonumber(EventID, Year) as EventNumber

resident Temp

order by EventStartDate;

Drop table Temp;

[#3]

Can the autonumber function be used on more than one field?  For instance if I wanted to run the autonumber on year, event category and perhaps a few more fields?

[Gysbert_Wassenaar]

Yes, that's possible. You can concatenate the fields to autonumber the combination of the fields or for the combination that should determine the counter. For example autonumber(EventID&EventCategory, Year) or autonumber(EventtID, Year&EventCategory).

[#5]

Thanks, that worked great!