Positive and negative month sequence based on date column and indicator column

[PB41091]

Hi,

I have below data set. I want to create 3 output columns considering the conditions explained as follows.

1. if A is same, then based on Date and Column C, if column B is changed according to C column ( from N to Y) considering successive month-year, then create output columns as shown below.

Input:

A	B	Date	C
abc	q	11/30/2021	N
abc	q	12/31/2021	N
abc	q	1/31/2022	N
abc	q	2/28/2022	N
abc	m	3/31/2022	Y
lmn	j	8/31/2020	N
lmn	j	9/30/2020	N
lmn	s	12/31/2020	N
lmn	s	1/31/2021	N
lmn	s	2/28/2021	N
lmn	s	3/31/2021	N
lmn	s	4/30/2021	N
lmn	s	5/31/2021	N
lmn	g	6/30/2021	Y
lmn	g	7/31/2021	Y
lmn	g	8/31/2021	Y
pvr	e	1/31/2021	N
pvr	e	2/28/2021	N
pvr	t	11/30/2021	N
pvr	t	12/31/2021	N
pvr	t	1/31/2022	N
pvr	o	3/31/2021	Y

Output: (Conversion flag_o1), O2, O3

A	B	Date	C	Coversion flag_O1	O2	O3
abc	q	11/30/2021	N			-4
abc	q	12/31/2021	N			-3
abc	q	1/31/2022	N			-2
abc	q	2/28/2022	N			-1
abc	m	3/31/2022	Y	1	0	0
lmn	j	8/31/2020	N
lmn	j	9/30/2020	N
lmn	s	12/31/2020	N			-6
lmn	s	1/31/2021	N			-5
lmn	s	2/28/2021	N			-4
lmn	s	3/31/2021	N			-3
lmn	s	4/30/2021	N			-2
lmn	s	5/31/2021	N			-1
lmn	g	6/30/2021	Y	1	0	0
lmn	g	7/31/2021	Y		1	1
lmn	g	8/31/2021	Y		2	2
pvr	e	1/31/2021	N			-2
pvr	e	2/28/2021	N			-1
pvr	t	11/30/2021	N
pvr	t	12/31/2021	N
pvr	t	1/31/2022	N
pvr	o	3/31/2021	Y	1	0	0

[SPetrie]

Im not entirely sure what your criteria are when evaluating what to change/create.

For example, why does PVR/t have nothing in the new columns but PVR/e does?

You should be able to create these rows using some multi-row tools. You just need to better define what triggers the new values.

I can replicate the first 2 new columns, but again, I dont know if I am actually understanding what your rules should be.

[PB41091]

Hi SPetrie, 

Thanks for your revert.

To clarify your doubt : "For example, why does PVR/t have nothing in the new columns but PVR/e does?"

Column O3 is dependent on column B, date and column C.

PVR/t , just observe dates.

pvr/o is "Y' in column C. So consider the date 3/31/21. . Last entry for pvr/t is on 1/31/22 with Column C as N. 

Date for pvr/o is not the successive month for last entry of pvr/t.

If we consider pvr/e, the last entry is on 2/28/21 with column C as "N". therefore if we consider pvr/o with 'Y' along with date 3.31/21 as conversion point, then the occurrence of  pvr/e is going to be negative series.

Same case for rest of the values in B column considering A column

[PB41091]

Hi,

Will you be able to display 2nd MultiRow formula completely?

[SPetrie]

Here is the workflow I came up with.

If I understand the rules correctly, this should work. 

Even if I am wrong, this should get you going.