Aggregations of sessions help

[Joe1]

Hi everyone,

I have a question about graphing some data.

I have a large dataset with multiple columns but the ones that matter are ID, date of session and weeks since registration (like the image - fake data). Essentially, I would like to plot a line graph with the week_since_registration on the x axis and the value of the line being the average number of sessions per week_since_registration. For example, in the first week from registration, an average of XX sessions across all ID’s (each row is a session). I have tried to do distintCount(date_of_session, [ID, Week_from_registration]) but that does not work - anyone have any ideas?

[David_Wong]

Is this the result you’re expecting?

[royyung]

@Joe1 can share an example
Do you expect column “weeks_since registration” to be shown on x axis?
Who you say "average number of sessions per week since registration, if I take the 1st as an example, what is the number you expect?
If I count the number of ID, it should be 5. or you expect it to be 4(1, 5,2,7) and divided by 6(1,2,3,5,6,7)?

[Joe1]

@royyung @David_Wong

Thank you both for your responses - apologies I wasn’t too clear in my initial request.

What I need is the average count for each week_since_registration.

For example, if we just look at weeks_since_registration == 1:
ID 1 has 2 sessions
ID 2 has 1 session
ID 3 has 0 sessions
ID 4 has 0 sessions
ID 5 has 1 session
ID 6 has 0 sessions
ID 7 has 1 session

Therefore I want (2 + 1 + 0 + 0 + 1 + 0+ 1)/7 = 0.71

and then the same for weeks_since_registration for weeks 2, 3, 4 and so on.

Then I want to plot the weeks_since_registration on the x axis and this calcualted value of the avg number of weekly sessions on the y axis.

Does that make sense?

[royyung]

You may create a calculated field like below to count the number of ID divided by distinct ID. However, ID 4 is not available in the example. I have manually added a row and assume it will be available in your dataset

countOver(ID,[{weeks_since_registration}],PRE_FILTER)/
distinctCountOver(ID,[],PRE_FILTER)

[Joe1]

@royyung

Thanks for that. It worked but now that I see the graph, doesn’t show what I thought it would.

How could I amend your calculated field to only include the sessions that did take place. For example, if I use my previous example:

If we just look at weeks_since_registration == 1:
ID 1 has 2 sessions
ID 2 has 1 session
ID 3 has 0 sessions
ID 4 has 0 sessions
ID 5 has 1 session
ID 6 has 0 sessions
ID 7 has 1 session

Therefore I want (2 + 1 + 1 + 1)/4 = 1.25

I have tried to amend the distinctcount statement to aggregate over the weeks_since_registration but that does not seem to work?

[David_Wong]

Does this look right?

[Joe1]

@David_Wong

Yes it does - that it exactly right, thanks for your help!