Hi all
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.
