I've been struggling with this issue for a long time now and I'm finally giving up and reach out to the forums. Even though I've been lurking the forums for answers for a long time this is the first time posting here so please excuse me if there's something missing.
I have a data set that contains user ids, activity types and dates. I want to extract the number of inactive periods for each user. The condition for a new inactivity period is defined as following: No inactive days for 5 days previously. E.g.
2018-01-01: Inactive (Period 1)
2018-01-04: Inactive (Period 1)
2018-02-05: Inactive (Period 2)
To make matters worse the data set is scattered with different activity types. A day can contain several rows for each user, so I cannot compare to previous rows, I need to compare the values by distance between the dates and do this for each unique user id. I've tried using table calculations to do a ascending count for each active day and compare the previous value once an inactive status is triggered (if count > 5 THEN "new period" ELSE "previous period" END) but this failed due to the logical expressions only working on a row level while my table calculations are on the table level. A downside with this version is also that I cannot extract the distinct count of periods for each user. In the end I'm looking to do a distinct count of inactive periods and if it's more than 6 periods for the last 12 months a "red flag"-value should be triggered so I can filter out the users going past that threshold.
After many trial & errors I have failed to get anywhere on this and I'm back at a blank workbook again so I have nothing but the raw data and a requested result to provide. I sincerely hope someone can shed some light on this matter or at least point me in the right direction
Activity_exampledata.xlsx 14.9 KB