Show data from current month plus 11
I have been reading the posts about filtering data by the current day/date. They haven't helped my question. I want to show forecast data for 12 months starting with the current month. All dates in the data set are first of the month.
Can I filter in the dataset with ETL?
Would it be better to filter in the card? If so, how would I do that?
Does this have to be done it SQL?
Thanks
Best Answers
-
You should be able to accomplish this with a calculated field as a filter.
CASE
WHEN `date_field` < DATE_ADD(CURDATE(), INTERVAL 12 MONTH)
AND `date_field` >= CURDATE()
THEN 'Next 12 Months'
ELSE 'Other'
ENDThis will calculate down to the day. You will need to change the logic if you are just interested in the monthly data. Something like this:
CASE
WHEN `date_field` < STR_TO_DATE(CONCAT(
MONTH(DATE_ADD(CURDATE(), INTERVAL 12 MONTH)),',',
'01',',',
YEAR(DATE_ADD(CURDATE(), interval 12 month))),'%m,%d,%Y')
AND `date_field` >= STR_TO_DATE(CONCAT(
MONTH(CURDATE()),',',
'01',',',
YEAR(CURDATE())),'%m,%d,%Y')
THEN 'Next 12 Months'
ELSE 'Other'
ENDthen put this field into the filter section and select "Next 12 Months"
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman3 -
Make sure to update the reference for next year's date calculation - and make it 13 months instead!
Update filter for next year criteria to be < instead of <=.
So - sorry for it being a little mixed up. You may have to flip between my 2 replies. Please ask me anything unclear. I'm used to just doing it. Working on getting the hang of communicating the solutions and juggling a million screenshots! I HATE that we can't just copy and paste the screenshots. Saving each one as an image, and uploading is tedious. 15 minute task turns into an hour! ...@DaniBoy ...
DataMaven
Breaking Down Silos - Building Bridges
**Say "Thanks" by clicking a reaction in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"2 -
@DHB_1 - I didn't realize that these were coming from two different data sets.
I noticed that in your example, you have the same date listed multiple times in the project tab. I'm assuming there is other meta data around those projects that are important to remain in the output of this join. If not, then you can skip steps 3 and 4 and just join the two data sets after the group by step.
Overview of ETLI created a Rank Function for Project_IDSum Revenue by MonthJoin monthly totals to original dataOnly select the columns neededJoin Sales forecast datafilter out extra columnsName output data set
Now you have a single date column and you should be able to filter that with this beast mode for the next 12 months:
CASE
WHEN `Month` <= STR_TO_DATE(CONCAT(
MONTH(DATE_ADD(CURDATE(), INTERVAL 12 MONTH)),',',
'01',',',
YEAR(DATE_ADD(CURDATE(), interval 12 month))),'%m,%d,%Y')
AND `Month` >= STR_TO_DATE(CONCAT(
MONTH(CURDATE()),',',
'01',',',
YEAR(CURDATE())),'%m,%d,%Y')
THEN 'Next 12 Months'
ELSE 'Other'
END
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman2
Answers
-
You should be able to accomplish this with a calculated field as a filter.
CASE
WHEN `date_field` < DATE_ADD(CURDATE(), INTERVAL 12 MONTH)
AND `date_field` >= CURDATE()
THEN 'Next 12 Months'
ELSE 'Other'
ENDThis will calculate down to the day. You will need to change the logic if you are just interested in the monthly data. Something like this:
CASE
WHEN `date_field` < STR_TO_DATE(CONCAT(
MONTH(DATE_ADD(CURDATE(), INTERVAL 12 MONTH)),',',
'01',',',
YEAR(DATE_ADD(CURDATE(), interval 12 month))),'%m,%d,%Y')
AND `date_field` >= STR_TO_DATE(CONCAT(
MONTH(CURDATE()),',',
'01',',',
YEAR(CURDATE())),'%m,%d,%Y')
THEN 'Next 12 Months'
ELSE 'Other'
ENDthen put this field into the filter section and select "Next 12 Months"
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman3 -
@ST_-Superman-_ is amazing with SQL Code.
Let me know if you would like me to put up an Magic ETL solution.
I just ask if we do that, and my ETL works, credit us both with the solution. @DaniBoy is able to make that happen if we need it. I don't want to be a solution swiper when there are always multiple ways to accomplish any task.
Let me know!
DataMaven
Breaking Down Silos - Building Bridges
**Say "Thanks" by clicking a reaction in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1 -
I'm working on learning ETL. My mind just thinks more in SQL right now so it is easier for me to find solutions that way. ?
If you want to show the ETL solution, that would help me learn as well! The more the merrier.
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman2 -
@ST_-Superman-_ - That's awesome! I need to get better at the SQL, so we balance out perfectly!
I will put up a solution. I just need to create it. I'm not as fast as you are. I don't know how you do it...
Well...yes I do...you're a freaking superhero! LOL
DataMaven
Breaking Down Silos - Building Bridges
**Say "Thanks" by clicking a reaction in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1 -
This seems too simple, so let me know if I am missing the point. Entirely possible.
Add column for Current Date - This updates dynamically.
Use date function widget to add 12 months to the current date.
Filter for the rows between today's date and next year's date.
I didn't add the output table to the screenshots because there may be other stuff you need to do - just don't forget to add one.
DataMaven
Breaking Down Silos - Building Bridges
**Say "Thanks" by clicking a reaction in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1 -
By the way - Since it was said that all the dates are the first of the month, I didn't construct a date for the first of the month...but...I just realized you do need it for the current date to get the first of the current month! Will post that, too.
DataMaven
Breaking Down Silos - Building Bridges
**Say "Thanks" by clicking a reaction in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
Make sure to update the reference for next year's date calculation - and make it 13 months instead!
Update filter for next year criteria to be < instead of <=.
So - sorry for it being a little mixed up. You may have to flip between my 2 replies. Please ask me anything unclear. I'm used to just doing it. Working on getting the hang of communicating the solutions and juggling a million screenshots! I HATE that we can't just copy and paste the screenshots. Saving each one as an image, and uploading is tedious. 15 minute task turns into an hour! ...@DaniBoy ...
DataMaven
Breaking Down Silos - Building Bridges
**Say "Thanks" by clicking a reaction in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"2 -
Thank you @DataMaven for your hard work here! You should be able to save these to your Dojo profile and then upload more rapidly to a post.
0 -
Thank you for all the input. I wasn't sure what to do with the formula from @ST_-Superman-_ but as I was adding it in Beast Mode and looking at the card filters, I found that there is a filter for Next 12 months. Hope that it updates next month.
The only way I've been able to keep learning is to read or ask questions in the community. The videos and instructions are good at first but are too simplistic. I will try the ETL from @DataMaven also.
Thanks again.
2 -
Would you mind sharing a sample of your data set and the card you are building? I will see if I can walk you through how to add the filter beastmode I posted.
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1 -
All I want is a single bar chart for each of the data sets. I've attached a simplified sample of the two data sets. I may want to make a sumo card also.
0 -
@DHB_1 we are so fortunate to have some Community rock stars such as @ST_-Superman-_ and @DataMaven helping out here.
We have an ongoing internal process to review the Community solutions created and cross check them with our standard documentation. Dojo Community is taking us to the next level!
Thanks!
1 -
@DHB_1 - I didn't realize that these were coming from two different data sets.
I noticed that in your example, you have the same date listed multiple times in the project tab. I'm assuming there is other meta data around those projects that are important to remain in the output of this join. If not, then you can skip steps 3 and 4 and just join the two data sets after the group by step.
Overview of ETLI created a Rank Function for Project_IDSum Revenue by MonthJoin monthly totals to original dataOnly select the columns neededJoin Sales forecast datafilter out extra columnsName output data set
Now you have a single date column and you should be able to filter that with this beast mode for the next 12 months:
CASE
WHEN `Month` <= STR_TO_DATE(CONCAT(
MONTH(DATE_ADD(CURDATE(), INTERVAL 12 MONTH)),',',
'01',',',
YEAR(DATE_ADD(CURDATE(), interval 12 month))),'%m,%d,%Y')
AND `Month` >= STR_TO_DATE(CONCAT(
MONTH(CURDATE()),',',
'01',',',
YEAR(CURDATE())),'%m,%d,%Y')
THEN 'Next 12 Months'
ELSE 'Other'
END
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman2 -
It's two data sets with slightly different data in them. I'm not combining them but want two separate cards. Sorry that it was confusing. There are other columns in the project data but are not necessary for the output. I could have just as easily summed it up in the ETL as I did with the other forecast list.
0 -
I got the formula to work in a Sumo card! Thanks @ST_-Superman-_ .
0
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.4K Charting
- 709 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive