3 Replies Latest reply on Aug 2, 2018 3:58 AM by Yuriy Fal

    Create conditional activity periods

    Filip Wannerskog

      Hey folks,

      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: Active

      2018-01-01: Inactive (Period 1)

      2018-01-03: Active

      2018-01-04: Inactive (Period 1)

      2018-01-05: Active

      .....

      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

       

      Thanks!