I have data that looks like this. I have data showing the number of users who have continued to keep 'Flag A' enabled over time. How can I visualize this data effectively to understand its usage trends?
Timestamp | flag_enabled | flag_disabled | user_id
2025-01-01 00:00:00 | a,b | c | 1
2025-01-02 00:00:00 | b | a,c | 1
2025-01-03 00:00:00 | a,b | c | 1
2025-01-01 00:00:00 | a,b | c | 2
2025-01-02 00:00:00 | b | a,c | 2
Edit 1: Below queries are what I have so far, how I can use this information to plot a time chart that would count number of users, with time, that continued to have 'Flag A' on vs number of users, that disabled the Flag eventually. For example,
timestamp | CountFlagEnabled | CountFlagDisabled
2025-01-01 00:00:00 | 2 | 0
2025-01-02 00:00:00 | 0 | 2
2025-01-03 00:00:00 | 1 | 0
let EnabledFlags = UserFlags
| where flag_enabled contains "a"
| summarize FirstEnabled=min(Timestamp), LastEnabled=max(Timestamp) by user_id;
let DisabledFlags = UserFlags
| where flag_disabled contains "a"
| summarize LastDisabled=max(Timestamp) by user_id;
let MergedFlags = EnabledFlags
| join kind=leftouter (DisabledFlags) on user_id
| project user_id, FirstEnabled, LastEnabled, LastDisabled;
// Count number of users that continued to keep 'Flag A' on
let UsersContinuedFlagA = MergedFlags
| where isnull(LastDisabled) or LastEnabled > LastDisabled
| summarize CountOfUsersContinued=count();
// Count number of users that disabled 'Flag A' after enabling it
let UsersDisabledFlagA = MergedFlags
| where isnotnull(LastDisabled) and LastEnabled <= LastDisabled
| summarize CountOfUsersDisabled=count();
Edit 1: updated data
On observing your input data, I have noticed few incorrect patterns. In the last row, when the flagA
is not in enabled column, then it should be in the disabled column. The flags should be in any one column.
And coming to your sample output data, the last row of date 3rd had one enabled and one disabled even when there is only one user present for the date 2025-01-03 00:00:00
.
So, I have tried the code like below to achieve your requirement.
let UserFlags = datatable(Timestamp: datetime, flag_enabled: string, flag_disabled: string, user_id: int)
[
datetime(2025-01-01 00:00:00), "a,b", "c", 1,
datetime(2025-01-02 00:00:00), "b", "a,c", 1,
datetime(2025-01-03 00:00:00), "a,b", "c", 1,
datetime(2025-01-01 00:00:00), "a,b", "c", 2,
datetime(2025-01-02 00:00:00), "b", "a,c", 2
];
let res_table = UserFlags
| extend flag_a_yes = iff(flag_enabled contains "a", 1, 0)
| extend no_flag_a = iff(flag_disabled contains "a", 1, 0)
| summarize
CountFlagEnabled = dcountif(user_id, flag_a_yes == 1 and no_flag_a == 0),
CountFlagDisabled = dcountif(user_id, flag_a_yes == 0 and no_flag_a == 1)
by Timestamp
| order by Timestamp asc;
This will give the res_table
result like below.
Then, you can get the time chart on this table as shown below.
res_table
| render timechart
if you change the input data like below
let UserFlags = datatable(Timestamp: datetime, flag_enabled: string, flag_disabled: string, user_id: int)
[
datetime(2025-01-01 00:00:00), "a,b", "c", 1,
datetime(2025-01-02 00:00:00), "b", "a,c", 1,
datetime(2025-01-03 00:00:00), "a,b", "c", 1,
datetime(2025-01-01 00:00:00), "a,b", "c", 2,
datetime(2025-01-02 00:00:00), "a,b", "c", 2
];
It will change the chart as below.