Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
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?

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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
Gysbert_Wassenaar

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

Not applicable
Author

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


talk is cheap, supply exceeds demand
Not applicable
Author

Thanks, that worked great!