Detect changes between samples of the same table

Question & Answer
1

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!

2

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

3

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

4

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

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

5

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

6

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!

7

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