rank login attempts

[jamesddelaney]

i have a pp table of usernames and a date time stamp of each login. so if a user logged into a site 5 times, he would have 5 entries in the table.

i'd like to add a column to this table call login number.

[TABLE="width: 500"]
<tbody>[TR]
[TD]username
[/TD]
[TD]login_datetime
[/TD]
[TD]login_number
[/TD]
[/TR]
[TR]
[TD]james
[/TD]
[TD]1/1/12 5:00 AM
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]james
[/TD]
[TD]1/1/12 11:00 AM
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]james
[/TD]
[TD]1/2/12 6:00 AM
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]james
[/TD]
[TD]2/1/12 5:00 AM
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]james
[/TD]
[TD]3/10/12 5:00 AM
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]john
[/TD]
[TD]1/3/12 4:00 PM
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]john
[/TD]
[TD]1/3/12 8:00 PM
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]john
[/TD]
[TD]1/3/12 11:00 PM
[/TD]
[TD]3
[/TD]
[/TR]
</tbody>[/TABLE]

what formula would you use to do the calculation for "login_number".

 

[Robert Mika]

Excel 2010
	A	B	C
1	username	login_datetime	login_number
2	james	1/1/2012 5:00	1
3	james	1/1/2012 11:00	2
4	james	1/2/2012 6:00	3
5	james	2/1/2012 5:00	4
6	james	3/10/2012 5:00	5
7	john	1/3/2012 16:00	1
8	john	1/3/2012 20:00	2
9	john	1/3/2012 23:00	3
Sheet5

Cell Formulas
Range	Formula
C2	=COUNTIF($A$2:A2,A2)

Drag formula down

 

[jamesddelaney]

this data is a powerpivot table, not in excel. any thoughts on how to do it in a powerpivot table?

 

[masplin]

=RANKX(FILTER(Table1,[username]=EARLIER[username],[login_datetime],,1)

Change Table1 to name of your table. The "1" ranks from smallest to largest. See below for more info.

http://technet.microsoft.com/en-us/library/gg492185

Mike

 

[jamesddelaney]

do i need PowerPivot 2012 RTM to make this work?

 

[masplin]

ah yes sorry they only put the ranking function in the new version.

 

[jamesddelaney]

i have taken this a bit further but i an now running into this error:

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

I also had to change around the syntax of your formula just a bit but given the error i am guessing i didn't do it quite right.

=RANKX(Table1,FILTER(Table1,[username]=EARLIER([username])),[login_datetime],1,skip)

thanks.

 

[masplin]

Sorry sloppy with my syntax. "skip" is the default so havent put it after the 1. The ",," before the 1 is because the "value" expression goes here (i'm not quite sure what "value" means!)

=RANKX(FILTER(Table1,[username]=EARLIER([username])),[login_datetime],,1)