Show data from current month plus 11

DHB_1
DHB_1 Member
in Magic ETL

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

  • ST_-Superman-_
    ST_-Superman-_ Coach
    Answer ✓

    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
  • DataMaven
    DataMaven Coach
    Answer ✓

    Month and Year.PNGFormat MonthDate as Date.PNG

    Make sure to update the reference for next year's date calculation - and make it 13 months instead!

    Update NextYear Ref.PNG

    Update filter for next year criteria to be < instead of <=. 

    Update Filter.PNG

    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"
  • ST_-Superman-_
    ST_-Superman-_ Coach
    Answer ✓

    @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 ETLOverview of ETLI created a Rank Function for Project_IDI created a Rank Function for Project_IDSum Revenue by MonthSum Revenue by MonthJoin monthly totals to original dataJoin monthly totals to original dataOnly select the columns neededOnly select the columns neededJoin Sales forecast dataJoin Sales forecast datafilter out extra columnsfilter out extra columnsName output data setName 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

Answers

  • ST_-Superman-_
    ST_-Superman-_ Coach
    Answer ✓

    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
  • DataMaven
    DataMaven Coach

    @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"
  • ST_-Superman-_
    ST_-Superman-_ Coach

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

     


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • DataMaven
    DataMaven Coach

    @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"
  • DataMaven
    DataMaven Coach

    This seems too simple, so let me know if I am missing the point. Entirely possible.  

     

    Add column for Current Date - This updates dynamically.  

    Add CurrDate.PNG

    Use date function widget to add 12 months to the current date. 

    Next Year.PNG

     

    Filter for the rows between today's date and next year's date. 

    Filter on Date Range.PNG

     

    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"
  • DataMaven
    DataMaven Coach

    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"
  • DataMaven
    DataMaven Coach
    Answer ✓

    Month and Year.PNGFormat MonthDate as Date.PNG

    Make sure to update the reference for next year's date calculation - and make it 13 months instead!

    Update NextYear Ref.PNG

    Update filter for next year criteria to be < instead of <=. 

    Update Filter.PNG

    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"
  • [Deleted User]
    [Deleted User] Member

    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
    DHB_1 Member

    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-_
    ST_-Superman-_ Coach

    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
  • DHB_1
    DHB_1 Member

    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]
    [Deleted User] Member

    @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-_
    ST_-Superman-_ Coach
    Answer ✓

    @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 ETLOverview of ETLI created a Rank Function for Project_IDI created a Rank Function for Project_IDSum Revenue by MonthSum Revenue by MonthJoin monthly totals to original dataJoin monthly totals to original dataOnly select the columns neededOnly select the columns neededJoin Sales forecast dataJoin Sales forecast datafilter out extra columnsfilter out extra columnsName output data setName 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
  • DHB_1
    DHB_1 Member

    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
    DHB_1 Member

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

Categories