How can I calculate Current Month's Visits/Working Day * Next Month's Working Days?
The picture is of my current card. I want to use April's Current Visits/BD (Work day) * May's Work Days to get May's Projected Visits, same for at least a 3 month projection. I have tried different forecasting options in the card and am not getting May's Data or correct projections for the other data. The Arrived and Budgeted Data shown is calculated in the ETL Directly. The Interval Formulas I have tried, do not calculate the next month's data how I need. Is there a solution in Beast mode to use the calculation below to project by month for the next 3 months?
The Current Visits/BD calculation is:
(SUM(case when Appointment Status
= 'Arrived' then Visit Count
end) / (MAX(COUNT(DISTINCT case when Appointment Status
= 'Arrived' then Billable Date
end)) over (partition by Appointment Start Date
))) + Visits/BD
The Current Visits Interactive calculation is:
SUM(case when Appointment Status
= 'Arrived' then Visit Count
end) +
(((SUM(case when Appointment Status
= 'Arrived' then Visit Count
end) / (MAX(COUNT(DISTINCT case when Appointment Status
= 'Arrived' then Billable Date
end)) over (partition by Appointment Start Date
))) + Visits/BD
)
*
((max(Count of Billable Day
) over (partition by Appointment Start Date
)) - ((MAX(COUNT(DISTINCT case when Appointment Status
= 'Arrived' then Billable Date
end)) over (partition by Appointment Start Date
)))))
Best Answer
-
Argh. I was trying to stay off the forum so others can answer. But I recently had to solve this for myself.
I created an ETL with an aggregate tile grouped by whichever fields were needed. With the visits in an aggregate and the amounts in another aggregate:The trick here is the count distinct values.
Then AFTER this group by step. Another group by step. Calculating current year and previous year…Year To Date. If you don't want YTD, remove the third line that has the less then equals.
CY YTD Visitssum(
case when YEAR(date
) = YEAR(CURRENT_DATE())
andtdate
<= CURRENT_DATE()
thenvisits
else 0 end
)PY YTD Visits
sum(
case when YEAR(date
) = YEAR(DATE_ADD(CURRENT_DATE(),-365))
anddate
<= DATE_ADD(CURRENT_DATE(),-365)
thenvisits
else 0 end
)
I had a lot of formulas for quarter, month, etc. I did those calculations after my group by steps.** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0
Answers
-
Argh. I was trying to stay off the forum so others can answer. But I recently had to solve this for myself.
I created an ETL with an aggregate tile grouped by whichever fields were needed. With the visits in an aggregate and the amounts in another aggregate:The trick here is the count distinct values.
Then AFTER this group by step. Another group by step. Calculating current year and previous year…Year To Date. If you don't want YTD, remove the third line that has the less then equals.
CY YTD Visitssum(
case when YEAR(date
) = YEAR(CURRENT_DATE())
andtdate
<= CURRENT_DATE()
thenvisits
else 0 end
)PY YTD Visits
sum(
case when YEAR(date
) = YEAR(DATE_ADD(CURRENT_DATE(),-365))
anddate
<= DATE_ADD(CURRENT_DATE(),-365)
thenvisits
else 0 end
)
I had a lot of formulas for quarter, month, etc. I did those calculations after my group by steps.** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 747 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive