I have a radar chart where the field is people (football players) and the data point is the number of times they received a the football from the currently selected person, where this person's ID matches another field e.g. Passer ID. So for this to work I am using set analysis to ignore the selected person but I also need this person's ID to match the Passer ID which creates my paradox. The same Player ID needs to be recognised and ignored.
So the dimension is the surname of the currently selected Player ID, the expression is a count of rows for any other Player ID where the selected Player ID (as a variable) = Passer ID, while also disregarding this selected Player ID.
Every Player ID will be selected in sequence afterward as this has to be automated. Passer ID is derived in the load script through a few resident tables.
I hope this makes sense and if there is a way to work it I would be really happy.
regardless of what chart you use, i would think you just need a relationship between Passer and Passed to. you will then need 3 fields: Passer, PassedTo, and instances. in your expression you just need to total instances
now if a Passer is selected - it will give you the number of instances he passed the ball to each person. so not sure what you need to disregard, you dont even need set analysis for this.
im not sure what you mean by the passer id being selected automatically in sequence in load scrpt.
So with some inspitation from the post above I decided to make a new aggregated table in the load script. Then in the chart the Receiver is the dimension and a count of Passes made to each teammate is the expression.
PasserID is derived in the load script, not selected in sequence, and it has no direct relationship to the selected Player for the chart (unless the selected Player is receiving the passes).
PasserID as Player,
Player as Receiver,
Event_Time as ReceiveTime,
LastName as ReceiverName
FROM (the main table which was stored as a qvd) (qvd);
count(DISTINCT ReceiveTime) as PassesMade
GROUP BY Receiver,Player,ReceiveTime,ReceiverName;
drop table PASSERPRE;