how to get last full 12 month in beast mode

WizardOz
WizardOz Contributor
edited April 2023 in Beast Mode

I would like to create a flag for the date for the last 12 full months, not including the current month.  For example, as of now, I only want to show any dates are in Oct 2018 to Nov 2017.  I used this one, but give me to the same date of last year. 

Thank you.

 

DATE_SUB(now(), INTERVAL 12 MONTH)  

 

Olivia

Best Answer

  • BlueRooster
    BlueRooster Domo Employee
    Answer ✓

    This will flag a 1 for 'in your range' and 0 for out.

     

    CASE WHEN `Date_Field` > LAST_DAY(DATE_SUB(current_date, INTERVAL 13 MONTH)) 
          AND `Date_Field <= LAST_DAY(DATE_SUB(current_date, INTERVAL 1 MONTH)) 
    THEN 1 
    ELSE 0 
    END

    That should hopefully help get you what you need.

     

    Sincerely,
    Valiant

     

    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.

Answers

  • BlueRooster
    BlueRooster Domo Employee
    Answer ✓

    This will flag a 1 for 'in your range' and 0 for out.

     

    CASE WHEN `Date_Field` > LAST_DAY(DATE_SUB(current_date, INTERVAL 13 MONTH)) 
          AND `Date_Field <= LAST_DAY(DATE_SUB(current_date, INTERVAL 1 MONTH)) 
    THEN 1 
    ELSE 0 
    END

    That should hopefully help get you what you need.

     

    Sincerely,
    Valiant

     

    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.

  • Derreck
    Derreck Member

    How can this be changed to include prior year 12 months (24 months ago)?

Categories