Tracking changes over time

[florinaturlea]

Hello,

I have the below example:

Object	Flag	Date
A	0	01.09.2023
A	1	02.09.2023
A	0	03.09.2023
A	0	04.09.2023
A	1	05.09.2023
B	0	01.09.2023
B	1	02.09.2023
B	0	03.09.2023
B	1	04.09.2023
C	1	01.09.2023
C	0	02.09.2023

This is just a subset of my data. I have a list of objects, each of them has a flag (1 or 0) that is changing over time. Following my business rules, the objects should change only once from 0 to 1.

But, in my data, I see objects A and B change 2 times.

For example: A's flag has set to 0 in 01.09.2023, changed to 1 in 02.09.2023, back to 0 in 03.09.2023 and then back to 1 in 05.09.2023. Same story with B, the change from 0 to 1 is happening twice.

Object C is ok, because the change from 0 to 1 is happening only once.

How can I search in this table and count the number of objects that are changing 2 times?

Thank you!

[vincent_ardiet_]

You want to do this identification in a script or in chart?

[florinaturlea]

Hi! Ideally, in a chart. 

[vincent_ardiet_ · Accepted solution]

If can create a table with "Object" as dimension.
And then an expression like this:

=If(    Min({<Flag={1}>Date)<>Max({<Flag={1}>Date)
    And Max({<Flag={0}>Date)> Min({<Flag={1}>Date)
    And Min({<Flag={0}>Date)< Max({<Flag={1}>Date)
    ,'Multiple changes'
    ,'Single change')

[florinaturlea]

Many thanks!!!