Forecasting Next Quarter's Data
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.
Best 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()) THENVisits
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 monthAverage Visits per Working Day
* 22** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0
Answers

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.
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 
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 AprilJune as well,
Thank you!
0 
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.
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 
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.
0 
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()) THENVisits
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 monthAverage Visits per Working Day
* 22** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0
Categories
 All Categories
 1.5K Product Ideas
 1.5K Ideas Exchange
 1.4K Connect
 1.1K Connectors
 283 Workbench
 4 Cloud Amplifier
 4 Federated
 2.7K Transform
 90 SQL DataFlows
 565 Datasets
 2K Magic ETL
 3.4K Visualize
 2.3K Charting
 593 Beast Mode
 13 App Studio
 28 Variables
 588 Automate
 143 Apps
 417 APIs & Domo Developer
 27 Workflows
 1 DomoAI
 28 Predict
 12 Jupyter Workspaces
 16 R & Python Tiles
 361 Distribute
 99 Domo Everywhere
 260 Scheduled Reports
 2 Software Integrations
 96 Manage
 93 Governance & Security
 15 Product Releases
 Community Forums
 37 Getting Started
 28 Community Member Introductions
 90 Community Announcements
 4.8K Archive