Active Record Count as of Certain Date

Randyb

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

Randyb

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)

Randyb

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

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

Guillermo

Randyb

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

Hi Randy, 

 

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

 

J'Von

Randyb

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