Sum of Value based on specific date parameters
I have a Card that is in Table view and I'm trying to populate multiple columns that are all summing a value based on specific date parameters. The issue I'm having is trying to set the parameters to autopopulate based on Current date.
For example I want the sum of flagged values which match certain date parameters, at the start of the year I use the following Beastmode and it returns the correct number:
Sum(Case when (`hireDate`) <='01/01/2017' And `dateOfTermination`is null
Then 1 else 0
End)
+
Sum(Case when (`hireDate`)<='01/01/2017' And `dateOfTermination`>='01/01/2017'
Then 1 else 0
End)
Now, if i want to see what this number is at the end of the quarter, two quarters prior from Current Date I can't get the result to return correctly and this is what I need to figure out. Again, if I hard code the date such as the following:
SUM(Case when (`hireDate`) <='06/30/2017' And `dateOfTermination`is null
Then 1 else 0
End
+
Case when (`hireDate`)<='06/30/2017' And `dateOfTermination`>='06/30/2017'
Then 1 else 0
End)
What can i input into the Beast Mode so that it can take the current Quarter Year which would be 42017 and subtract 2 quarters from that which would make it 22017 and have the calculations work.
Any insight would be greatly appreciated
Thanks
Randy
Best Answer
-
Hi,
If I correctly understood your problem this modification should help you:
SUM(Case when (`hireDate`) <=DATE_SUB(LAST_DAY(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'/',QUARTER(CURDATE())*3,'/01'),'%Y,%m,%d')), interval 2 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 2 QUARTER) And `dateOfTermination`>=DATE_SUB(LAST_DAY(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'/',QUARTER(CURDATE())*3,'/01'),'%Y,%m,%d')), interval 2 QUARTER)
Then 1 else 0
End)I replaced your date with a formula that gives you the last day of the current quarter minus 2 quarters.
Tell me how it goes.
Hope this helps.
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
-
Is anyone able to help out with this request?
0 -
Hi,
If I correctly understood your problem this modification should help you:
SUM(Case when (`hireDate`) <=DATE_SUB(LAST_DAY(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'/',QUARTER(CURDATE())*3,'/01'),'%Y,%m,%d')), interval 2 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 2 QUARTER) And `dateOfTermination`>=DATE_SUB(LAST_DAY(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'/',QUARTER(CURDATE())*3,'/01'),'%Y,%m,%d')), interval 2 QUARTER)
Then 1 else 0
End)I replaced your date with a formula that gives you the last day of the current quarter minus 2 quarters.
Tell me how it goes.
Hope this helps.
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 -
Thanks for your help, I knew it must be possible and I was on the right track with the Beast Mode I was trying to build but that was getting a bit advanced for me.
Really appreciate it.
Randy
0
Categories
- All Categories
- 1.4K Product Ideas
- 1.4K Ideas Exchange
- 1.4K Connect
- 1.2K Connectors
- 284 Workbench
- 4 Cloud Amplifier
- 4 Federated
- 2.9K Transform
- 89 SQL DataFlows
- 556 Datasets
- 2.2K Magic ETL
- 3.3K Visualize
- 2.3K Charting
- 569 Beast Mode
- 11 App Studio
- 27 Variables
- 578 Automate
- 140 Apps
- 414 APIs & Domo Developer
- 23 Workflows
- 1 DomoAI
- 28 Predict
- 12 Jupyter Workspaces
- 16 R & Python Tiles
- 350 Distribute
- 90 Domo Everywhere
- 258 Scheduled Reports
- 2 Software Integrations
- 92 Manage
- 89 Governance & Security
- 9 Product Release Questions
- Community Forums
- 42 Getting Started
- 28 Community Member Introductions
- 85 Community Announcements
- 4.8K Archive