Views

Question

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 · Answer

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

mroker · Answer

Thanks @Kenda , I am getting the following:

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

Kenda · Answer

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!