Complex scenario for comaring two values based on earlier date

[Santosh_K]

Hi All,

I have below dataset

LOAD * Inline [
Name, Movies, Date
ravi ,A, 2016-01-01
ravi ,B, 2016-02-15
nitin ,B ,2016-02-24
nitin, A, 2016-02-25
sam ,A, 2016-03-03
sam ,B, 2016-03-04
mat ,A, 2016-04-06
john, A, 2016-04-15
john, B, 2016-04-17 ]

I want to find the distinct users who watched Movie A before Movie B.

So based on requirements and above data set,  it should return 3 count. Because users ravi, sam & john have watched Movie A before B.

Note: I want to perform this calculation on front end expression using set analysis.

[stevejoyce]

aggr(if(min({<Movies = {'A'}>} Date) < min({<Movies = {'B'}>} Date), 1) ,Name)

I chose min for A and min for B so that if someone watched multiple times, this will only flag if the first time they watched A was before they ever watched B. 

[Saravanan_Desingh]

Try this,

=Count(DISTINCT {<Name={"=Concat(DISTINCT Movies,'',Date)='AB'"}>}Name)

[Kushal_Chawda · Accepted solution]

@Santosh_K  try below set

=Count(DISTINCT {<Name={"=min({<Movies = {'A'}>} Date)<min({<Movies = {'B'}>} Date)"}>}Name)

[Santosh_K]

If I modify your expression as below it works but looking for set expression if possible

sum(aggr(if(min({<Movies = {'A'}>} Date) < min({<Movies = {'B'}>} Date), 1) ,Name))

[Santosh_K]

Your expression does not work if user watched movies multiple time

[Santosh_K]

This works. Thank you all as leraned something from each reply