I am making a chess app and want to have a visualization that preforms similar to market basket analysis. chart table I have a dimension of 5 of the most used first moves. and I want a measure that is the second move that lead to the most wins for black. here is close to what I came up with to show what I want but it doesn't work because of a few reasons.
firstsortedorder(Moves,-aggr(count(distinct{<winner={'black'},nbr={2}>} Game_id),Moves)))
1. firstsortedorder will not look at the table dimension an create a value for each instance.
2. if I try to do it outside of a table I run into the issue of wanting to first look at only the first moves and than only looking at the second move.
FIELDS
Moves-chess moves in standard notation eg. e4, d4
winner-black,white,draw
Game_ID- primary key for all 20,000 games in dataset
nbr-move number in game
so the result i am trying to get is if white plays e4 and the reply e5 lead to the most wins for black it will return the value e5 if white plays Kf3 and the reply that lead to the most wins was Kf6 it will return that. Ideally it would be a table format so i can just lists whites moves and blacks best replies
