This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Please refer the example below. I need to fix data sample table to Expected result table.
This is what I want CHECKMRY = Month and year of CHECKDATE except if Cycle = 500 (last 3 digits of location, example: if location is 0000000006700100, then the cycle is 100. 0000000006700500, then cycle is 500) then the CHECKMRY will be the previous Month and year. If an account is a 500 cycle, then if the CHECKDATE is 02-04-19 then the CHECKMRY is 1901. If the CHECKDATE = 01-14-19 then the CHECKMRY is 1812.
My formulas might be messy but I hope I answered your question. I just parsed the year and month from the CHECKDATE, then defined a "Month v2" and "Year v2" which applies the logic of having the CYCLE = 500 where the previous monthyear will be returned. See below formulas.
Note that rows 3 to 5 do not have a cycle = 500 so their month-year have been retained.
Attached is the workflow I created. Hope this helps.