Skip to content

    • Beast Mode

    Beast Mode

    Forecasting Next Quarter's Data

    Member
    in Beast Mode

    I am trying to take the current month's visits/ Working day to forecast the next 3 months total visits per month. I would take current month's visits/ working day * next month's working days. I want the card to be interactive where there is a visits/Working day control toggle as well. I am getting stuck on the date formula based on Forecasted date to use current months data.

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Best Answer

    • Coach
      Answer ✓

      To apply the logic you mention, you'll need to come up with a formula for the avg of the current months visits. And count working days.

      CASE WHEN WEEKDAY(Date) NOT IN (1, 7) THEN 1 ELSE 0 END

      And something to calculate the average visit per working day.

      SUM(CASE WHEN YEAR(Date) = YEAR(CURRENT_DATE()) AND MONTH(Date) = MONTH(CURRENT_DATE()) THEN Visits ELSE 0 END) / SUM(CASE WHEN WEEKDAY(Date) NOT IN (1, 7) THEN 1 ELSE 0 END)

      And maybe assume a typical 22 working days month

      Average Visits per Working Day * 22

      ** Was this post helpful? Click Agree or Like below. **
      ** Did this solve your problem? Accept it as a solution! **

    Answers

    • Coach

      I've shared my method recently. I create formulas for CY, PY, P2Y (current year, previous year, previous 2 year)….or month such as month to date compared to previous year month to date…..using a formula where the formula compares the date in data with the current date. If you want year to date or month to date, add a line where date is less than or equal to current date.

      1. sum(
      2. case when YEAR(date) = YEAR(CURRENT_DATE())
      3. and date <= CURRENT_DATE()
      4. then visits else 0 end
      5. )

      If you go by month, add a line for month:

      1. sum(
      2. case when YEAR(date) = YEAR(CURRENT_DATE())
      3. and MONTH(date) = MONTH(CURRENT_DATE())
      4. and date <= CURRENT_DATE()
      5. then visit_amount else 0 end
      6. )

      The would give you current month to date. In the first example I count visits. In the second I count amount made from those visits. The same type of formula can be applied to your forecast.

      ** Was this post helpful? Click Agree or Like below. **
      ** Did this solve your problem? Accept it as a solution! **

    • Member
      image.png

      This is the formula I used to test the logic for next month,

      image.png

      This is the data its returning. I want it to show March's Data for April-June as well,

      Thank you!

    • Coach

      The formula might be something like:

      1. sum(
      2. case when YEAR(date) = YEAR(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH))
      3. and MONTH(date) = MONTH(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH))
      4. then visit else 0 end
      5. )

      Assuming you have entries for visits next month. Change the interval to get other future months.

      ** Was this post helpful? Click Agree or Like below. **
      ** Did this solve your problem? Accept it as a solution! **

    • Member

      April, May, and June, do not have visits. My end goal is to use March's Visits/ Work Day * each month's Work Day to get forecasted visits. This is a test of the logic that will be applied to more in depth formulas with interactive variables. I updated the formula to what you suggested and its returning the same values.

    • Coach
      Answer ✓

      To apply the logic you mention, you'll need to come up with a formula for the avg of the current months visits. And count working days.

      CASE WHEN WEEKDAY(Date) NOT IN (1, 7) THEN 1 ELSE 0 END

      And something to calculate the average visit per working day.

      SUM(CASE WHEN YEAR(Date) = YEAR(CURRENT_DATE()) AND MONTH(Date) = MONTH(CURRENT_DATE()) THEN Visits ELSE 0 END) / SUM(CASE WHEN WEEKDAY(Date) NOT IN (1, 7) THEN 1 ELSE 0 END)

      And maybe assume a typical 22 working days month

      Average Visits per Working Day * 22

      ** Was this post helpful? Click Agree or Like below. **
      ** Did this solve your problem? Accept it as a solution! **

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories