Record count for prior month or prior quarter at start of the year
I have a Beast Mode that I've been using that was working fine up until January 1st. The issue is that I have a number of Beast Modes that are calculating record counts for Prior Month, Prior Quarter and Prior 2 Quarters past.
They all seem to have trouble getting past the 2018 component, which I know I've coded in the Beast Mode because otherwise it was pulling quarter and month info for numerous years instead of the current year.
I assume that somehow I need to build in a case statment that checks to see what the year month is and based on that will alter the Beast Mode.
Here is a sample of what I'm using for Prior Month Beast Mode, the current Quarter -1 and Current Quarter -2 are very similar.
Let me know what I should do or if this is possible.
thanks
Randy
Sum(CASE WHEN `hireDate` <= DATE_ADD(CONCAT(MONTH(CURRENT_DATE()),'-1-',YEAR(CURRENT_DATE())), interval -1 day ) AND `dateOfTermination`IS NULL THEN 1 ELSE 0 END
+
CASE WHEN `hireDate` <= DATE_ADD(CONCAT(MONTH(CURRENT_DATE()),'-1-',YEAR(CURRENT_DATE())), interval -1 day ) AND `dateOfTermination` >= DATE_ADD(CONCAT(MONTH(CURRENT_DATE()),'-1-',YEAR(CURRENT_DATE())), interval -1 day )
Then 1 else 0
End)
Best Answer
-
Hi,
Can you try this one out:
Sum(CASE WHEN `hireDate` <= DATE_SUB(LAST_DAY(CURDATE()), interval 1 MONTH) AND `dateOfTermination`IS NULL THEN 1 ELSE 0 END
+
CASE WHEN `hireDate` <= DATE_SUB(LAST_DAY(CURDATE()), interval 1 MONTH) AND `dateOfTermination` >= DATE_SUB(LAST_DAY(CURDATE()), interval 1 MONTH)
THEN 1 ELSE 0 END)This code: DATE_SUB(LAST_DAY(CURDATE()), interval -1 MONTH) will give you the last day of the previous month.
Tell me how it goes.
Ricardo Granada
MajorDomo@Lusiaves
**If the post solves your problem, mark it by clicking on "Accept as Solution"
**You can say "Thank you" by clicking the thumbs up in the post that helped you.0
Answers
-
Hi,
Can you try this one out:
Sum(CASE WHEN `hireDate` <= DATE_SUB(LAST_DAY(CURDATE()), interval 1 MONTH) AND `dateOfTermination`IS NULL THEN 1 ELSE 0 END
+
CASE WHEN `hireDate` <= DATE_SUB(LAST_DAY(CURDATE()), interval 1 MONTH) AND `dateOfTermination` >= DATE_SUB(LAST_DAY(CURDATE()), interval 1 MONTH)
THEN 1 ELSE 0 END)This code: DATE_SUB(LAST_DAY(CURDATE()), interval -1 MONTH) will give you the last day of the previous month.
Tell me how it goes.
Ricardo Granada
MajorDomo@Lusiaves
**If the post solves your problem, mark it by clicking on "Accept as Solution"
**You can say "Thank you" by clicking the thumbs up in the post that helped you.0 -
I tried it but it still didn't work, it produce the same values as the current month and current quarter which tells me its still not breaking out of 2018.
That is the issue I'm having is that right now everything is returning 01 2018 as the month year and won't go back to 12 2017 for the prior month and won't go back 10 2017 etc for the prior Quarters.
Once I'm far enough into 2018 everything would be fine again but for the next 6 months my current approach isn't going to work.
Let me know if you have any other ideas.
Thanks for you help
Randy
0 -
Hi
Maybe your problem is not on the BeastMode I tried that in my account and it's returning the date 2017/12/31 for the current date.
Are your "hiredate" and "dateOfTermination" DateTime fields?
Regards,
Ricardo Granada
MajorDomo@Lusiaves
**If the post solves your problem, mark it by clicking on "Accept as Solution"
**You can say "Thank you" by clicking the thumbs up in the post that helped you.0 -
The date of termination is a date time field however the time value doesn't change it is always 18:00
I'm digging into the results detail and doing a comparison using hard coded dates to validate one more time, it may actually be working.
Stay tuned.
Randy
0 -
I've got a bouple of other issues going on that I discovered but I think this is working, thanks for your help
Randy
0 -
Hi, I have tested the proposed solution
DATE_SUB(LAST_DAY(CURDATE()), interval -1 MONTH)
and it breaks for February, it says 29th of January is no longer in the "last month", but in the "current month".
So I am using this formula, instead
CASE
WHEN DATE(`Payment Date`) > SUBDATE(CURRENT_DATE(), interval DAYOFMONTH(CURRENT_DATE()) DAY) THEN 'This Month'
WHEN DATE(`Payment Date`) < SUBDATE(CURRENT_DATE(), interval DAYOFMONTH(CURRENT_DATE()-1) DAY)
AND DATE(`Payment Date`) > SUBDATE(SUBDATE(CURRENT_DATE(), interval DAYOFMONTH(CURRENT_DATE()) DAY), interval DAYOFMONTH(SUBDATE(CURRENT_DATE(), interval DAYOFMONTH(CURRENT_DATE()) DAY)) DAY) THEN 'Last Month'
ELSE 'Not current or previous month'
END
0 -
Hi @user055174
You could also utilize the last_day function to make it a bit simpler. LAST_DAY just returns the last day in the month for a given date.
CASE WHEN LAST_DAY(`dt`) = LAST_DAY(CURRENT_DATE()) THEN 'This Month' WHEN LAST_DAY(`dt`) = LAST_DAY(DATE_SUB(CURRENT_DATE(), interval 1 month)) THEN 'Last Month' WHEN LAST_DAY(`dt`) = LAST_DAY(DATE_ADD(CURRENT_DATE(), interval 1 month)) THEN 'Next Month' ELSE 'Other Month' END
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive