Show data from current month plus 11

DHB_1

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

ST_-Superman-_

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"

DataMaven

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 ...

 

 

ST_-Superman-_

@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

ST_-Superman-_

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"

DataMaven

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

 

 

ST_-Superman-_

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.

 

DataMaven

@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

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

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

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 ...

 

 

[Deleted User]

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.

DHB_1

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.

ST_-Superman-_

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.

DHB_1

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. 

[Deleted User]

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

ST_-Superman-_

@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

DHB_1

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.

DHB_1

I got the formula to work in a Sumo card! Thanks @ST_-Superman-_ .