I am looking to create a chart which will show overlaps between people associated with different events.
For example, we might have table like this
So the idea is to create a chart (or a table?) where I will have both X and Y axis with the names of people (column Person), and my coordinate (place where lines meet) is a count of overlaps;
Person A overlaps with himself on three events, on two events with person B and one event with person C
Person B overlaps with person A on two events, attends there events himself and shares one event with person C...
Person C shares one event with person A or person B and attends two events in total.
Now, I can create two tables during load with separate names for columns and then create chart with unions, but this is then one_off event and if I do something with filtering (for example time period) I will have to reload everything with that in mind.
Is there a way to do that dinamicaly for a chart? I did it before as a pivot table, but I guess same thing should be possible with scatter chart?
(p.s. special consideration is that I have hundreds of thousands of records for which I wish to calculate unions)
I looked at the sample, but this solution requires intervention during loading script; I think it should be possible to do within chart itself without creating another table and/or column in the table.
Idea was to have dimension Person as x and y axis, and then calculate union based on count (eventID)
I was thinking that I could to that with aggregate (but that appears not to be the case), I tried to use same dimension for X and Y axis (and it creates proper chart but only shows result where I have same x and y value all other fields are null)...
Maybe set analysis... (but I am not proficient enough yet)
(not to mention that my data model is much more complex and would multiply data involved)