trying to create a beast mode calc
Hello, Can you please assist on helping me with creating a beast mode calculation.
I've been trying to create one but it doesn't seem to calculate what I need.
The column dimension I have is a ticket count of how many tickets were created on a date entered for each day of the month.
The rows are different departments. The pivot table displays how many tickets are created for each department each day of the month.
Now I need a column that calculates the average of the total tickets dependent on how many days have passed for that month.
ex. if 10 days have passed for the month, I need to get that average of the total tickets for each row for the past 14 days.
and so forth, 14 days, 18 days, 19, 20…
any assistance is greatly appreciated.
Best Answers
-
Perhaps if you divided that by the day of the month, like this:
SUM(COUNT(
TicketNbr
)) OVER (PARTITION BY board_name ORDER BYdate_entered
)/ DAYOFMONTH(date_entered)
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.2 -
Hmm no problem @Abe ,
If you all ever need, I teach a course on how to use Domo:
Feel free to book 30 with me if you want to show me what you're trying to do:
John Le
Let me make you the me of your organization for Domo
Click here for more video solutions:
1
Answers
-
Hi @Abe ,
Can you send a screenshot?
John Le
Let me make you the me of your organization for Domo
Click here for more video solutions:
0 -
Sorry, Here is the screenshot of the data- I need an average of each of the rows whether its 5 days into the month or 10, 15, so on.
I started with this calc- SUM(
date_entered
)/DAYOFMONTH(date_entered
) but that doesn't work and i've been playing around with it but getting no where,0 -
Hi @Abe ,
So this is going to be tough if every column is a date. You really need to have one column as date and then you can do some pivot action. I'd recommend doing these steps:
- Do a dynamic unpivot to put all the dates in one row
- Then you can write a formula to do some like case when
- Rest and contemplate life
Let me know if that makes sense otherwise I can do some time with you
John Le
Let me make you the me of your organization for Domo
Click here for more video solutions:
0 -
Have you tried using a window function to get a running total? It would look something like this:
SUM
(SUM(`ticket_number`))
OVER
(PARTITION BY board_name ORDER BY `date_entered`)
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
@MarkSnodgrass , thank you for the advice. I tried using the formula that you provided but the SUM of the ticket number seems like it's adding the actual ticket number and not the count, so I changed it to
SUM(COUNT(
TicketNbr
)) OVER (PARTITION BY board_name ORDER BYdate_entered
)But the Avg column for each date is totaling each day that passes. What would I need to change on the formula that would Avg the number of day it passes from the total count of tickets. Example- 12/3 date would have 28 total tickets / 3 days = 9.3 avg for the first row.
0 -
Hi @DashboardDude ,
Thank you for your advice.
I tried to create a new board per your advice but I could not figure out how to get a dynamic unpivot table.
Sorry, we just started Domo few months ago and still getting to know the system,
0 -
Perhaps if you divided that by the day of the month, like this:
SUM(COUNT(
TicketNbr
)) OVER (PARTITION BY board_name ORDER BYdate_entered
)/ DAYOFMONTH(date_entered)
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.2 -
Thank you sir! I believe that did the trick. But for some reason, Domo is not able to save the card after adding the new formula. Does the system not recognize the formula?
0 -
Hmm no problem @Abe ,
If you all ever need, I teach a course on how to use Domo:
Feel free to book 30 with me if you want to show me what you're trying to do:
John Le
Let me make you the me of your organization for Domo
Click here for more video solutions:
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
- 102 SQL DataFlows
- 626 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 754 Beast Mode
- 61 App Studio
- 41 Variables
- 693 Automate
- 178 Apps
- 456 APIs & Domo Developer
- 49 Workflows
- 10 DomoAI
- 38 Predict
- 16 Jupyter Workspaces
- 22 R & Python Tiles
- 398 Distribute
- 115 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 130 Manage
- 127 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 11 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 110 Community Announcements
- 4.8K Archive