Detect changes between samples of the same table

Hi all!

I have a table like this and I want to be able to detect changes in “enabled” column from 1 to 0 between consecutive samples (one sample per month).

month       name        enabled

2022-01-01  client_A    1
2022-01-01  client_B    1
2022-01-01  client_C    1
2022-02-01  client_A    1
2022-02-01  client_B    0
2022-02-01  client_C    1
2022-02-01  client_D    1
2022-02-01  client_E    0

I can get something like this:

month       total_clients
2022-01-01  3
2022-02-01  3

But what I really need is to get the count of changes from 1 to 0 (or 0 to 1) between consecutive samples:

For example, count changes from 1 to 0:

month       losses
2022-01-01  0
2022-02-01  1 (disabled client_B)

The other way, count changes from 0 to 1:

month       gains
2022-01-01  3 (because no previous sample, all clients are "new")
2022-02-01  1 (new client_D enabled)

client_E is new, but is not enabled, so it is not a gain nor a lose.

Appretiate any hint or idea on this!

you may consider using functions such as Period Over Period Computation or percent Difference to check the value difference by each month. Then apply a filter to show difference > 1

Roy, thanks for your reply!
I have tried periodOverPeriod but without success. Can you help me with some examples using this functions?

I have created this calculated field
`periodOverPeriodDifference(sum(enabled),month)!
image

sorry. seems some problem with the image before. I just uploaded

Sorry Roy, I can’t see the image attached.

Wow! That’s great.

Can I then get the count of all 1’s in PeriodOverPeriodDiff to get “gains” and the count of all -1’s in PeriodOverPeriodDiff to get “loses”?

If it’s possible, then that’s it.

Thank you again!

I’ve just checked and seems impossible to nest aggregation functions.