Forum Discussion
Count If Measure For Last 3 Months Above Value
I have a table of data that is in the format below:
| Category | Month | Value 1 | Value 2 | % Value |
| A | Aug 2020 | 10 | 100 | 10% |
| A | Sep 2020 | 25 | 100 | 25% |
| A | Oct 2020 | 20 | 100 | 20% |
| A | Nov 2020 | 30 | 100 | 30% |
| B | Aug 2020 | 30 | 100 | 30% |
| B | Sep 2020 | 25 | 100 | 25% |
| B | Oct 2020 | 20 | 100 | 20% |
| B | Nov 2020 | 5 | 100 | 5% |
I need to calculate how many categories have achieved a % value >= 20% for the last 3 consecutive months rolling. So in the example above for Nov the count should be 1 (category A) as the % value was >= 20% for the last 3 months rolling.
I'm not sure what the best way is to achieve this? I was thinking if I ended up with something like below than I can just sum the 3rd column for each of the months so for Oct there is 1 category that ended up achieving >= 20% for previous 3 months and for November there is also 1 category that ended up achieving >=20% for previous 3 months.
| Category | Month | >= 20% for last 3 months |
| A | Oct 2020 | 0 |
| A | Nov 2020 | 1 |
| B | Oct 2020 | 1 |
| B | Nov 2020 | 0 |
But if anyone has any better ideas? The Month value is from a date calendar, so it not initially grouped by month. I need the % value grouped by month before summing them over the 3 months rolling.
17 Replies
- AngelaMarie
Helper II
Redoing the tables as they are a bit difficult to see in the last post
This is the first table
Category__ __Month__ __Value 1__ __Value 2__ __% Value__ A Aug 2020 10 100 10% A Sep 2020 25 100 25% A Oct 2020 20 100 20% A Nov 2020 30 100 30% B Aug 2020 30 100 30% B Sep 2020 25 100 25% B Oct 2020 20 100 20% B Nov 2020 5 100 5% The Second table:
__Category__ __Month__ __>= 20% last 3 months__ A Oct 2020 0 A Nov 2020 1 B Oct 2020 1 B Nov 2020 0 - AnonymousNot applicable
AngelaMarie
Can you also post a expect outcome based on the sample you provided, I will go for a test.Regards
Paul- AngelaMarie
Helper II
I would like to be able to show the count of categories >20% for the 3 month rollingd. Something like below
Month
Jan 2020 Feb 2020 Mar 2020 Category Count > 20% 2 4 3 So in the above example under January 2020, there were 2 categories that achieved >20% for Nov 2019, Dec, 2019 and Jan 2020