Forum Discussion

AngelaMarie's avatar
AngelaMarie
Icon for Helper II rankHelper II
5 years ago
Solved

Count If Measure For Last 3 Months Above Value

I have a table of data that is in the format below:

CategoryMonthValue 1Value 2% Value
AAug 20201010010%
ASep 20202510025%
AOct 20202010020%
ANov 20203010030%
B

Aug 2020

3010030%
BSep 20202510025%
BOct 20202010020%
BNov 202051005%

 

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. 

CategoryMonth>= 20% for last 3 months
AOct 20200
ANov 20201
BOct 20201
BNov 20200

 

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

  • 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__
    AAug 20201010010%
    ASep 20202510025%
    AOct 20202010020%
    ANov 20203010030%
    BAug 20203010030%
    BSep 20202510025%
    BOct 20202010020%
    BNov 202051005%

     

     

    The Second table:

     

    __Category____Month____>= 20% last 3 months__
    AOct 20200
    ANov 20201
    BOct 20201
    BNov 20200
    • Anonymous's avatar
      Anonymous
      Not applicable

      AngelaMarie 

      Can you also post a expect outcome based on the sample you provided, I will go for a test.

       

      Regards
      Paul

      • AngelaMarie's avatar
        AngelaMarie
        Icon for Helper II rankHelper II

        I would like to be able to show the count of categories >20% for the 3 month rollingd. Something like below

         

        Month

        Jan 2020Feb 2020Mar 2020
        Category Count > 20%243

         

        So in the above example under January 2020, there were 2 categories that achieved >20% for Nov 2019, Dec, 2019 and Jan 2020