I have a session events table which captures pageviews which looks a bit like this:
SessionID
SessionEventId
DateTime
Page
EventType
1
1
11/03/2021+093144
Start
PageView
1
2
11/03/2021+093201
Intro
PageView
1
3
11/03/2021+093230
DataEntry
PageView
2
4
etc
Start
PageView
2
5
etc
Intro
PageView
3
6
etc
Start
PageView
4
7
etc
Start
PageView
4
8
etc
Intro
PageView
I want to make a bar chart with the drop-offs at each page. I can't really do this in the data load editor as I need the pages to be linked to a separate table which has the page order so I can order by page order:
Page
Order
Start
1
Intro
2
DataEntry
3
I've managed to create a table of last pages by session using =FirstSortedValue(Page,-DateTime) and =FirstSortedValue(SessionEventId,-DateTime):
SessionID
SessionEventId
Page
1
3
DataEntry
2
5
Intro
3
6
Start
4
8
Intro
I thought using Count(aggr(FirstSortedValue(SessionEventId,-DateTime),Distinct SessionID)) in a table where Page is a dimension would do what I needed but instead it just sums all the distinct SessionIDs and puts them against Start.
Page
What I want
What I'm getting
Start
1
4
Intro
2
0
DataEntry
1
0
I think I'm not quite wrapping my head around dimensions properly and how they work in charts. Any help would be appreciated.
I've made some progress! So by setting LastPageSeen =aggr(FirstSortedValue(Page,-TimeStamp),SessionID) as a dimension in the table then I can do Count(Distinct SessionID) to get counts by last page seen.
What's missing in this is the ordering now of LastPageSeen! It doesn't seem to respond if I try order it by Order. So now the quetsion is how do I link a calculated dimension to another dimension so I can get the ordering in
