Limited date options
1. Year to Date and Current year seem to yield same results. Is this a bug?
2. Common period measure of MAT (moving annual total) and MQT (moving quarterly total) seems to be missing.
3. Can system recognise what is the latest completed month, instead of admin manually adjusting period when new monthly data arrives?
Best Answer
-
1. Year to Date and Current Year will be the same results if your data set does not have any future dates in it. If you have data points from July and August, etc. and you select "Current Year" they will show up in the results. If you select "Year to Date" they will not be in the results.
2. Moving Annual Total - We usually use the "Last 365 Days" option to get a "rolling 12 month" view of sales, etc.
3. I set up a beast mode to filter out the current months data. Something like this:
CASE WHEN YEAR(`Invoice_Date`)=YEAR(CURDATE())
AND MONTH(`Invoice_Date`)=MONTH(CURDATE())
THEN 'true' else 'false'
END
You can then put this field in your "filters" section and select to only look at the "false" results. This will then automatically include June dates once we get to July 1st.
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1
Answers
-
Year to Date and Current Year should return the same as they're the same time period.
Moving Annual and Moving Quarterly can be done by choosing Last 12 Months (or Last 4 Quarters or Last 1 Year or Last 365 Days), same for moving quarterly. There are many ways to return the same time period.
If you want the last completed month, just use the Previous Month as your default time period.
Hope that helps,
ValiantSpur
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.1 -
1. Year to Date and Current Year will be the same results if your data set does not have any future dates in it. If you have data points from July and August, etc. and you select "Current Year" they will show up in the results. If you select "Year to Date" they will not be in the results.
2. Moving Annual Total - We usually use the "Last 365 Days" option to get a "rolling 12 month" view of sales, etc.
3. I set up a beast mode to filter out the current months data. Something like this:
CASE WHEN YEAR(`Invoice_Date`)=YEAR(CURDATE())
AND MONTH(`Invoice_Date`)=MONTH(CURDATE())
THEN 'true' else 'false'
END
You can then put this field in your "filters" section and select to only look at the "false" results. This will then automatically include June dates once we get to July 1st.
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1 -
Thank you for your excellent solution. Regarding beast filtering out current month, how would you modify the code if I also wanted to filter out previous month? Thanks again. Andrew
0
Categories
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 472 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 477 Datasets
- 203 Visualize
- 255 Beast Mode
- 2.1K Charting
- 12 Variables
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 181 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive