Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Count number of dates that happen before the given date

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?

Tags (2)
1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: Count number of dates that happen before the given date

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;


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Count number of dates that happen before the given date

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;


talk is cheap, supply exceeds demand

View solution in original post

Highlighted
Not applicable

Re: Count number of dates that happen before the given date

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?

Highlighted
MVP & Luminary
MVP & Luminary

Re: Count number of dates that happen before the given date

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).


talk is cheap, supply exceeds demand
Highlighted
Not applicable

Re: Count number of dates that happen before the given date

Thanks, that worked great!