Beast mode question
Hi all,
I have a beast mode calculation created which I'm trying to find out the monthly average of open tickets between 5 boards for the past 15 months and this is what I have-
SUM(COUNT(TicketNbr
)) OVER (PARTITION BY Board_Name
ORDER BY last_update
)/ DAYOFMONTH( last_update )
But I think the calculations are off. Is there something that I need to change, I can't quite figure it out.
Any assistance is greatly appreciated.
Best Answers
-
There are many syntax errors in what you typed. DAYOFWEEK(last_update) was used to find the weekend days. If you take out that reference, there's no need for DAYOFWEEK. You are also only putting your parens () around the first statement not the whole group. It was SUM({A}) / COUNT({B}). Etc.
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
Hey @Abe, try this:
count(distinct `Ticket ID`) / 30
The only issue with this formula is that it assumes all the months have 30 days. If that won't work for you, then you can go with a slightly more complicated but more accurate solution (it can be further refined if you want to account for leap years, but i left that off for simplicity's sake):
count(distinct `Ticket ID`) / (case
when month(`date_entered`) in (1,3,5,7,8,10,12) then 31
when month(`date_entered`) in (2) then 28
else 30
end)
Once you create this formula, you can replace the calculation that you have in the values section of your pivot table with it, and it should give you the desired answer.
0
Answers
-
Calculate the total number of open tickets per month.
Calculate the average number of open tickets per month.SUM(
CASE
WHEN last_update BETWEEN DATE_SUB(CURDATE(), INTERVAL 15 MONTH)
AND CURDATE()
THEN 1
ELSE 0
END)
/ COUNT(DISTINCT CASE WHEN last_update BETWEEN DATE_SUB(CURDATE(), INTERVAL 15 MONTH)
AND CURDATE()
THEN CONCAT(YEAR(last_update), MONTH(last_update))
ELSE NULL
END)** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
Sorry, this is calculating, Average daily (weekday) ticket queue by month
0 -
Hey Abe,
Could you share an example dataset and/or list the columns you have available?
Also, an example of the desired output?
Thanks,
0 -
How about using DAYOFWEEK(
last_update
) NOT IN (1, 7) to make sure only weekdays are used?SUM(CASE WHEN DAYOFWEEK(last_update) NOT IN (1, 7)
AND last_update BETWEEN DATE_SUB(CURDATE(), INTERVAL 15 MONTH)
AND CURDATE()
THEN 1
ELSE 0
END)
/
COUNT(DISTINCT CASE WHEN DAYOFWEEK(last_update) NOT IN (1, 7)
AND last_update BETWEEN DATE_SUB(CURDATE(), INTERVAL 15 MONTH)
AND CURDATE()
THEN CONCAT(YEAR(last_update), MONTH(last_update), DAY(last_update))
ELSE NULL END)** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **1 -
Thank you for the reply! Will this work for average daily ( weekday) ticket queue by month?
0 -
@ArborRose , getting a syntax error. I removed the 1,7, we still want to calculate the weekend
0 -
Thank you for the assistance. Essentially, we want to have a table that shows 5 Boards listing the average daily ticket queue in the given month for the past 15 months. Disregard the title of Total tickets- 8 weeks .
So the data should show - the average daily tickets for the month of Sept '23 is 25 tickets.
Then in Oct '23 it should show a similar average.mPlease see below:
0 -
There are many syntax errors in what you typed. DAYOFWEEK(last_update) was used to find the weekend days. If you take out that reference, there's no need for DAYOFWEEK. You are also only putting your parens () around the first statement not the whole group. It was SUM({A}) / COUNT({B}). Etc.
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
Hey @Abe, try this:
count(distinct `Ticket ID`) / 30
The only issue with this formula is that it assumes all the months have 30 days. If that won't work for you, then you can go with a slightly more complicated but more accurate solution (it can be further refined if you want to account for leap years, but i left that off for simplicity's sake):
count(distinct `Ticket ID`) / (case
when month(`date_entered`) in (1,3,5,7,8,10,12) then 31
when month(`date_entered`) in (2) then 28
else 30
end)
Once you create this formula, you can replace the calculation that you have in the values section of your pivot table with it, and it should give you the desired answer.
0 -
If you want to exclude weekdays from the calculation, simply create a column to do so:
case when day(`date_entered`) in (1,7) then 'Exclude' else 'Include' end
Then, in the filters section of analyzer, select only 'Include.'
Domo pivot tables will take care of the rest for ya.
1 -
Thank you both for the assistance. This greatly appreciated and it helped a lot.
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K 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
- 748 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
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 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
- 109 Community Announcements
- 4.8K Archive