Average based on Months Passed and Months left in year

Options
gump
gump Member
in Magic ETL

Hello,

Looking to take a goal I have already calculated and be able to show how much is needed on average per month until the end of the year. Additionally, calculate the average based on months passed.

An example would be a goal of 500 topics posted during the year and currently there are 250 topics posted. I have an ETL that calculates how many I need, in this example 250 topics to be posted to reach the goal. From there how can I show that with 2 months left in the year I would need 125 topics posted per month ie. Topics needed/months left in year.

Secondly, how can I show the average amounts of topics posted in the prior 10 months ie. new topics this year/months passed.


Thank you

Comments

  • GrantSmith
    GrantSmith Coach

    Are you wanting just a simple number or are you wanting to graph this over time?

    If you're wanting a simple number:

    Topics needed per month:

    (MAX(`Goal`) - SUM(`Topics`))
/ (12 - MONTH(CURDATE()) + 1) -- +1 to include the current month

    Average amount of topics prior 10 months:

    CASE WHEN MONTH(CURDATE()) <> 1 THEN  -- protect against divide by 0 error for the start of the year
  SUM(`Topics`)
  /
  (MONTH(CURDATE()) - 1) -- Don't include the current month
END
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • gump
    gump Member

    Hi @GrantSmith,

    Thank you for the answer! I am looking to graph this over time instead of a just a simple number. Any further help with that would be appreciated!

Categories