Dynamic Annualized Number- Using Hard Coded Number Now for the Month

NJHARR36
NJHARR36
in Magic ETL

I have a beast mode that is currently using a hard coded number for the month (i.e 7 for July) and I want to make it dynamic to update each pervious month we close.  Below is my current beast mode, which works but is not dynamic:

 

(SUM(case when `Period`>='2019-01-01' and `Period`<'2020-01-01' and `Ledger Type`='AA' and `Suppress Plan Month`='No' then `Amount`  else 0 end)/7)*12

Best Answer

  • guitarhero23
    guitarhero23 Contributor
    Answer ✓ 
    (SUM(case when `Period`>='2019-01-01' and `Period`<'2020-01-01' and `Ledger Type`='AA' and `Suppress Plan Month`='No' then `Amount`  else 0 end)/MONTH(CURRENT_DATE()))*12

    Try doing this MONTH(CURRENT_DATE()) 

     

    Edit: Oh wait when you say previous month are you saying that this month being August (8) you want the report to be the month before aka /7 not 8, then in September you'd want /8.

     

    IF so use MONTH(DATE_SUB(CURDATE(), 1 month))


    **Make sure to like any users posts that helped you and accept the ones who solved your issue.**

Answers

  • guitarhero23
    guitarhero23 Contributor

    Just to clarify, are you saying the only number/date you're looking to make dynamic is the 7 in the divided by 7 section? Or you are also looking to make the '2019-01-01' and '2020-01-01' dynamic?


    **Make sure to like any users posts that helped you and accept the ones who solved your issue.**
  • NJHARR36
    NJHARR36

    @guitarhero23 I am only look to change the 7.  Thanks for reviewing!  

  • guitarhero23
    guitarhero23 Contributor
    Answer ✓ 
    (SUM(case when `Period`>='2019-01-01' and `Period`<'2020-01-01' and `Ledger Type`='AA' and `Suppress Plan Month`='No' then `Amount`  else 0 end)/MONTH(CURRENT_DATE()))*12

    Try doing this MONTH(CURRENT_DATE()) 

     

    Edit: Oh wait when you say previous month are you saying that this month being August (8) you want the report to be the month before aka /7 not 8, then in September you'd want /8.

     

    IF so use MONTH(DATE_SUB(CURDATE(), 1 month))


    **Make sure to like any users posts that helped you and accept the ones who solved your issue.**
  • NJHARR36
    NJHARR36

    @guitarhero23 THANK YOU!

  • guitarhero23
    guitarhero23 Contributor

    You got it pal! Smiley Very Happy


    **Make sure to like any users posts that helped you and accept the ones who solved your issue.**

Categories