Views

[mroker]

UserID	Channel	Timeslot
4	25	08:30
4	25	09:00
4	25	09:30
5	21	08:30
5	21	09:00
6	20	08:30
6	17	09:00
7	22	08:30
7	22	09:00
7	21	09:30

Above, what I would like to show is users that have watched a channel once, twice, or three times. The caveat is, this can only be back to back. So if a user watches at 8:00, then goes to another channel at 9:00, then comes back at 9:30 to that channel, then it is only considered 1x for each view. If the user stays on the same channel for multiple timeslots, it can be 2x, 3x.

For example:

 User 4 has watched the same channel (25) in three timeslots back to back.

User 5 has watched the same channel (21) two slots back to back

User 6 has watched only 1 channel (20) once, and another (17) once

User 7 has watched channel 22 back to back but has only watched channel 21 once

This may be unclear, but hopefully someone can help!

[Kenda]

Hi @mroker 

In this case, I think you'll want to try out the multi-row formula tool. First make sure your data is sorted by user then time. Next, use a multi-row formula tool to create a new numeric field (below called New Field), grouping by user, with the following expression:

iif([Channel]=[Row-1:Channel],[Row-1:New Field]+1, 1)

With this, you're basically making a count that increases whenever the same channel continues back to back for each person. 

From here, you can use a summarize to get the max count per person per channel, or something.

Hope this helps! 

[mroker]

Thanks @Kenda , I am getting the following:

Unknown variable "Row-1:CHAN,[Row-1:New Field"

[Kenda]

@mroker Look closely at your expression. You may be missing some brackets around field names.