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 April-June 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