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.7K Product Ideas
 1.7K Ideas Exchange
 1.5K Connect
 1.2K Connectors
 295 Workbench
 6 Cloud Amplifier
 8 Federated
 2.8K Transform
 97 SQL DataFlows
 608 Datasets
 2.1K Magic ETL
 3.8K Visualize
 2.4K Charting
 709 Beast Mode
 49 App Studio
 39 Variables
 667 Automate
 170 Apps
 446 APIs & Domo Developer
 44 Workflows
 7 DomoAI
 33 Predict
 13 Jupyter Workspaces
 20 R & Python Tiles
 391 Distribute
 111 Domo Everywhere
 274 Scheduled Reports
 6 Software Integrations
 115 Manage
 112 Governance & Security
 Domo Community Gallery
 31 Product Releases
 9 Domo University
 5.3K Community Forums
 40 Getting Started
 30 Community Member Introductions
 103 Community Announcements
 4.8K Archive