Forecasting Next Quarter's Data

Jenna

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.

ArborRose

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

ArborRose

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.

sum(

case when YEAR(date) = YEAR(CURRENT_DATE())

and date <= CURRENT_DATE()

then visits else 0 end

)

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

sum(

case when YEAR(date) = YEAR(CURRENT_DATE())

and MONTH(date) = MONTH(CURRENT_DATE())

and date <= CURRENT_DATE()

then visit_amount else 0 end

)

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.

Jenna

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

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

Thank you!

ArborRose

The formula might be something like:

sum(

case when YEAR(date) = YEAR(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH))

and MONTH(date) = MONTH(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH))

then visit else 0 end

)

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

Jenna

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.

ArborRose

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