Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all, I'm trying to identify rows in a given month that satisfy the same condition for 3 consecutive months. I've already created a new column in the table that calculates whether the conditino is met (1=yes, 0=no). What I ultimately want to end up with is a graph showing number of records that have met the condition in 3 consecutive months and I don't know how to structure it. Example:
Device | Month | Condition met |
ABC | Sep 2020 | 1 |
BCD | Sep 2020 | 0 |
CDE | Sep 2020 | 0 |
ABC | Oct 2020 | 1 |
BCD | Oct 2020 | 0 |
CDE | Oct 2020 | 1 |
ABC | Nov 2020 | 1 |
BCD | Nov 2020 | 0 |
CDE | Nov 2020 | 0 |
This would calculate for that the most recent month only one Device met the condition for 3 consecutive months:
Device | Month | Count |
ABC | Nov 2020 | 1 |
BCD | Nov 2020 | 0 |
CDE | Nov 2020 | 0 |
Can anyone help me calculate this, the result of which I want to graph by month. TIA!!
Solved! Go to Solution.
Is this what you are looking for?
Ahmedx, thanks for the reply! This gets me almost there. It definitely let's me identify the number of devices that met the condition for 3 consecutive months in the most recent month (MaxDate). I would like to capture and graph that number(count) for each of the last x# months, not just the most recent. So I would want to calculate the number in 'flag fixed' in your example per month. Can you think of a way to do that? Thx.
User | Count |
---|---|
92 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |