Active Record Count as of Certain Date

Randyb
Randyb Member
in Magic ETL

I'm trying to figure out the best approach for a Beastmode or other suggestion, to count the number of Active Records in a data set for a given period of time.

Each Record has a Started Date and may or may not have an Ended Date (Determines the value of Active or In Active)

I would like to be able to report the Sum or Count of Active Records by Month in a Chart. I've created a BeastMode that flags as a value of 1 if it is active regardless of the end date so its easy to Sum those records.

I need to figure out a way to basically filter on the Ended date by month.

Could this be as easy as Doing the reverse flag for the terminated and having a Beastmode that Subtracts the Inactive from the Active?

Randy

Best Answer

  • Randyb
    Randyb Member
    Answer ✓

    I have resolved it, although quite a bit more complex than what I was hoping for but it works very well, here is a copy of the Beastmode that is gathering the total active records for the prior quarter. I have a similar one for each date range I'm pulling for, by current month, prior month, prior quarter, prior 2 quarters past etc.

    Let me know if you need more help on this.

     

    Randy

     

    SUM(Case when (`hireDate`) <=DATE_SUB(LAST_DAY(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'/',QUARTER(CURDATE())*3,'/01'),'%Y,%m,%d')), interval 0 QUARTER) And `dateOfTermination`is null
    Then 1 else 0
    End
    +
    Case when (`hireDate`)<=DATE_SUB(LAST_DAY(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'/',QUARTER(CURDATE())*3,'/01'),'%Y,%m,%d')), interval 0 QUARTER) And `dateOfTermination`>=DATE_SUB(LAST_DAY(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'/',QUARTER(CURDATE())*3,'/01'),'%Y,%m,%d')), interval 0 QUARTER)
    Then 1 else 0
    End)

Answers

  • Randyb
    Randyb Member

    Further Information possible progress

    I've modified my Beast Mode and I can now flag a record as being Active, Term Today or Future Term. If I use CurrentDate() as the date requirement everything is listed as active which makes sense.

    I then changed the CurrentDate() to 06/01/2017 as shown below and it gives me the Active Record Status as of June 1st 2016 which is what I'm after.

    How do I make this so that I can display the results by Month without having to modify the Beast Mode each time, I'd really like to see these results as Count of Active By Month Current Year.

    Case
    when `dateOfTermination`> '06/01/2017' Then 'FutureTerm'
    when `dateOfTermination`= '06/01/2017' Then 'Term Today'
    Else 'Active'

    End

     

    Any ideas ??

  • gespindola
    gespindola Member

    Hi Randy! Have you resolved this case? I need the same thing! 

    Guillermo

  • Randyb
    Randyb Member
    Answer ✓

    I have resolved it, although quite a bit more complex than what I was hoping for but it works very well, here is a copy of the Beastmode that is gathering the total active records for the prior quarter. I have a similar one for each date range I'm pulling for, by current month, prior month, prior quarter, prior 2 quarters past etc.

    Let me know if you need more help on this.

     

    Randy

     

    SUM(Case when (`hireDate`) <=DATE_SUB(LAST_DAY(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'/',QUARTER(CURDATE())*3,'/01'),'%Y,%m,%d')), interval 0 QUARTER) And `dateOfTermination`is null
    Then 1 else 0
    End
    +
    Case when (`hireDate`)<=DATE_SUB(LAST_DAY(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'/',QUARTER(CURDATE())*3,'/01'),'%Y,%m,%d')), interval 0 QUARTER) And `dateOfTermination`>=DATE_SUB(LAST_DAY(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'/',QUARTER(CURDATE())*3,'/01'),'%Y,%m,%d')), interval 0 QUARTER)
    Then 1 else 0
    End)

  • JVon
    JVon Member

    Hi Randy, 

     

    Thank you so much for sharing! Would you also share your beastmode for current month?

     

    J'Von

  • Randyb
    Randyb Member

    Here is the Beast Mode I'm using to get the current month count, there are two variables I'm looking for in this Beast Mode and I'm just adding the two together, if you only need to measure one varialbe then you could just drop one of them:

     

    Case when YEARWEEK(`hireDate`)<=YEARWEEK(CURDATE()) And `dateOfTermination`is null
    Then 1 else 0
    End
    +
    Case when YEARWEEK(`hireDate`)<=YEARWEEK(CURDATE()) And YEARWEEK(`dateOfTermination`)>=YEARWEEK(CURDATE())
    Then 1 else 0
    End

Categories