Active Record Count as of Certain Date
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
-
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)0
Answers
-
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 ??
1 -
Hi Randy! Have you resolved this case? I need the same thing!
Guillermo
1 -
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)0 -
Hi Randy,
Thank you so much for sharing! Would you also share your beastmode for current month?
J'Von
0 -
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
End0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 712 Beast Mode
- 50 App Studio
- 39 Variables
- 668 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 45 Workflows
- 7 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 116 Manage
- 113 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 104 Community Announcements
- 4.8K Archive