How to count status changes in snapshot data

helpme12345
helpme12345 Member
in Magic ETL

I have data that I snapshot daily. It has employee name and employee status. Once the status gets updated, it remains that way: ex for one employee:

1/1 Name: active

1/2 Name: active

1/3 Name: active

1/4 Name: Inactive

1/5 Name: Inactive

1/6 Name: Inactive

1/7: Name: Inactive

At the card level, I would like to be able to use the snapshot data to determine how many employees in a given week went from active to inactive. Any recommendations on how to do this? I was attempting to use Rank & Window function to and use a case statement to count # of inactive in a weeks timeframe, but it doesn't seem to be successful.

Tagged:

Best Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @renee12345 I would suggest using a dataflow to group your data by week and name. In the same group by tile add a distinct count of Status. Whenever the distinct count equals 2, then that indicates that that person's status changed within that same week.

  • MichelleH
    MichelleH Coach
    Answer ✓

    @renee12345 Do you just need a date field for your cards? If so you could use the formula below to identify the first day of the week to group by instead:

    `Snapshot Date` - DAYOFWEEK(`Snapshot Date`)

Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @renee12345 I would suggest using a dataflow to group your data by week and name. In the same group by tile add a distinct count of Status. Whenever the distinct count equals 2, then that indicates that that person's status changed within that same week.

  • helpme12345
    helpme12345 Member

    That makes sense! The issue I'm running into now is adding back in a date so I can visualize at the card level. I'm using the formula Week('Snapshot Date'), and when I add back in Snapshot Date it skews the counts. Any advice here? @MichelleH

  • MichelleH
    MichelleH Coach
    Answer ✓

    @renee12345 Do you just need a date field for your cards? If so you could use the formula below to identify the first day of the week to group by instead:

    `Snapshot Date` - DAYOFWEEK(`Snapshot Date`)

  • helpme12345
    helpme12345 Member

    Thank you @MichelleH !!

Categories