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

Question

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:

ABDateCabcq11/30/2021Nabcq12/31/2021Nabcq1/31/2022Nabcq2/28/2022Nabcm3/31/2022Ylmnj8/31/2020Nlmnj9/30/2020Nlmns12/31/2020Nlmns1/31/2021Nlmns2/28/2021Nlmns3/31/2021Nlmns4/30/2021Nlmns5/31/2021Nlmng6/30/2021Ylmng7/31/2021Ylmng8/31/2021Ypvre1/31/2021Npvre2/28/2021Npvrt11/30/2021Npvrt12/31/2021Npvrt1/31/2022Npvro3/31/2021Y

Output: (Conversion flag_o1), O2, O3

ABDateCCoversion flag_O1O2O3abcq11/30/2021N  -4abcq12/31/2021N  -3abcq1/31/2022N  -2abcq2/28/2022N  -1abcm3/31/2022Y100lmnj8/31/2020N   lmnj9/30/2020N   lmns12/31/2020N  -6lmns1/31/2021N  -5lmns2/28/2021N  -4lmns3/31/2021N  -3lmns4/30/2021N  -2lmns5/31/2021N  -1lmng6/30/2021Y100lmng7/31/2021Y 11lmng8/31/2021Y 22pvre1/31/2021N  -2pvre2/28/2021N  -1pvrt11/30/2021N   pvrt12/31/2021N   pvrt1/31/2022N   pvro3/31/2021Y100

SPetrie · Answer

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.

multirow.yxmd

PB41091 · Answer

Hi,

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

PB41091 · Answer

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