How to count status changes in snapshot data

renee12345

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.

MichelleH

@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

@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`)

MichelleH

@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.

renee12345

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

@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`)

renee12345

Thank you @MichelleH !!