Cartesian chart

[marko_rakar]

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

Person, EventID

a,1

a,2

a,3

b,1

b,3

b,5

c,1

c,5

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;

a	3	2	1
b	2	3	1
c	1	1	2
	a	b	c

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)

[Gysbert_Wassenaar]

That looks a lot like the problem solved in this discussion: Please help in distinct count

[marko_rakar]

thanks for the pointer,

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)

[Gysbert_Wassenaar]

I think it should be possible to do within chart itself without creating another table and/or column in the table.

If you can manage making it perform well too then I'm certainly interested.

[marko_rakar]

I have an idea, which is a hybrid but will most likely create smallest overhead of all (let me think this through)