Find intersection between previous values

[agni_gold]

Hi Friends,

I have one situation ,

I have below data for Month and ID

Month	ID	Required_Output
Dec-15	3	null
Jan-16	3	null
Feb-16	3	3
Mar-16	4	null
Apr-16	4	null
May-16	4	4

Now i want each row should  compare its previous 3 rows and find intersection value , i have placed required output in one column.

Example : For Feb-2016  , it will compare Dec-2015,Jan-2016 and Feb-2016 any ID: 3 is common in between , so 3 should return in front of Feb-2016 , else null

[vinieme12]

See attached

FACT:

LOAD Month,

  ID,

  Month&'_'&ID as KEYField

FROM

(ooxml, embedded labels, table is Sheet1);

left join(FACT)

Temp:

LOAD

ID,

ID as FLAG,

MAX(Month)&'_'&ID as KEYField

Resident FACT

Group by ID;

[vinieme12]

in Straight table you can try

=if(ID = Rangesum(above(ID,0,RowNo()))/3,ID,0)

[agni_gold]

But here i am not getting any intersection.

[vinieme12]

Not sure, what if?

Month	ID	Required_Output
Dec-15	3	null
Jan-16	3	null
Feb-16	3	3
Mar-16	4	null
Apr-16	5	null
May-16	5	?
Jun-16	4	?
Jul-16	4	?

[agni_gold]

Yes this i want , but it is not happening.

Can you please share app with set analysis. 

[vinieme12]

what should be the output here?

what if?

Month	ID	Required_Output
Dec-15	3	null
Jan-16	3	null
Feb-16	3	3
Mar-16	4	null
Apr-16	5	??
May-16	5	??
Jun-16	4	??
Jul-16	4	??

[agni_gold]

Please see below

Month	ID	Required_Output
Dec-15	3	null
Jan-16	3	null
Feb-16	3	3
Mar-16	4	null
Apr-16	5	null
May-16	5	null
Jun-16	4	null
Jul-16	4	null

[vinieme12]

OK, try the below

FACT:

LOAD Month,

  ID,

  if(RangeSum(ID,Peek(ID),Peek(ID,-2))/3=ID,ID,null()) as Flag

FROM

(ooxml, embedded labels, table is Sheet1);

[agni_gold]

Can we do it on front end , in set analysis ?