# 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

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'END`

This 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'END`

then 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.” -Superman
______________________________________________________________________________________________

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
Check out my video!
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"

@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.” -Superman
______________________________________________________________________________________________

• @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
Check out my video!
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
• 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.” -Superman
______________________________________________________________________________________________
• @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
Check out my video!
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
• This seems too simple, so let me know if I am missing the point. Entirely possible.

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
Check out my video!
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
• 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
Check out my video!
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
• domo

💎

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.

Dani aka "Mr.Dojo"

**Say "Thanks" by clicking the "heart" in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
**You can update your Dojo Community name and avatar by clicking on your avatar then the "My Profile" button.
• 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.

• 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.” -Superman
______________________________________________________________________________________________
• 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.

• domo

💎

@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!

Dani aka "Mr.Dojo"