Hi everyone, there are some question about pair two point in each day, and how many pair happen and how many user pair in one month?
like this image
I try this video method, but it only pair first and last.
Our data have pair many time in one day, so that really hard to count.
There are our simple data "Pair Point.xlsx"
Dose any idea to calculate it?
Wish somebody can help us thanks.
Hi HongKai,
I think it needs nested LOD expression to count pair users per month.
<formula>
Initial Char (this determines pair of points.)
LEFT([Point], 1)
# of Pair
{FIXED [test_date], [Initial Char], [User ID]: IF ZN(COUNTD([Point])) = 2 THEN 1 ELSE 0 END}
# of Pair User
{FIXED DATETRUNC('month', [test_date]): COUNTD({FIXED DATETRUNC('month', [test_date]), [User ID]: IF SUM([Pair]) > 0 THEN MIN([User ID]) END})}
<sample -- focused on Sep 2018>
Please check attached workbook.
