Booking date and shipment date formula
Hello I am trying to arrive at a beast mode calculation where the booking in current quarter if shipped in the current quarter then give me the shipment dollars. It has to be dynamic so that I don't have to change the formula every quarter.
Answers
-
I would use the QUARTER() function and the YEAR() function to ensure that you get the expected result. It would look something like this:
CASE WHEN QUARTER(
dt
) = QUARTER(CURRENT_DATE()) AND YEAR(dt
) = YEAR(CURRENT_DATE()) THEN 'Same'
ELSE 'Different'
END**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
Do you want the formula to return a date or the shipment dollars? What are the columns in your dataset?
0 -
To get the dollar amounts you can use Mark's logic to return the amount for the quarter:
CASE WHEN QUARTER(`dt`) = QUARTER(CURRENT_DATE()) AND YEAR(`dt`) = YEAR(CURRENT_DATE()) THEN `Shipment Dollars` END
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
I have two columns. One is the Booking date and the second is the shipment date. IF the Booking date and the shipment date are within the same quarter then give me the Shipment dollar for the Order number.
0 -
CASE WHEN QUARTER(`booking_date`) = QUARTER(`shipment_date`) AND YEAR(`booking_date`) = YEAR(`shipment_date`) THEN `Shipment Dollars` END
You'd want something like this then
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2 -
If you only want to show the orders that meet your criteria, you can create this beast mode and then drag into your filters and filter to include.
CASE WHEN QUARTER(
booking_date
) = QUARTER(shipment_date
) AND YEAR(booking_date
) = YEAR(shipment_date
) THEN
'Include'
ELSE 'Exclude'
END**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
In this case lets say the booking date is 1 October and the shipment date is 15th November then will the output be the Shipment dollars? Should I load a date mapping file to get the output?
0 -
If your dataset already has the booking date, shipment date, shipping dollars and order number, you shouldn't need any additional fields. Have you tried using any of the above suggestions in Analyzer? Start with a table card and see if it returns the results you expect.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 299 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 54 App Studio
- 40 Variables
- 681 Automate
- 175 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 9 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 33 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive