Average question
I am trying to find out the average Orders that my team does every week.
Some days we work 5 days, some 6, some 7.
So I want to find for week 6 of the year  What was my average Orders?
Orders done on (2/5 + 2/6 +2/7 + 2/8 +2/9) / 5
How can i create a beast mode for this?
Best Answer

@shreeyab if we zoom on only the row you highlighted, if I'm understanding properly, and assuming your
Orders
number is right (I just find it odd it has decimals), are you expecting to see the following?If this is the case, then you'll need to use the FIXED function, so that each day can look at more than the values for it, depending on whether your card will always be limited to a single week or not, the formula will require a BY clause or not (it requires it if you might end up showing multiple weeks in the same card). If a single week is shown (as filtered by the date range), something like this should work.
SUM(SUM(`Orders`) FIXED(BY `AssignedTo`))/AVG(COUNT(DISTINCT `ProductionScheduleDate`) FIXED(BY `AssignedTo`))
If it can show multiple weeks and you need the weekly average:
SUM(SUM(`Orders`) FIXED(BY `AssignedTo`, `ProductionScheduleDate`  WEEKDAY(`ProductionScheduleDate`)))/AVG(COUNT(DISTINCT `ProductionScheduleDate`) FIXED(BY `AssignedTo`, `ProductionScheduleDate`  WEEKDAY(`ProductionScheduleDate`)))
Whether the denominator needs to have the BY clause on the
AssignedTo
depends on whether for example in the case ofDallas
in your example it should be divided by 4 or 5. Here's an example using the second formula which is more flexible.0
Answers

How about this?
COUNT(`Orders`) / (CASE WHEN DAYOFWEEK(`Order Date`) = 2 THEN 5
WHEN DAYOFWEEK(`Order Date`) = 3 THEN 6 ELSE 7 END)
The CASE statement checks the day of the week of each order date:If the order date falls on Monday (DAYOFWEEK(Order Date) = 2), it means your team worked for 5 days in that week. This is because Monday is typically the start of the workweek, and we assume the team works Monday through Friday.
If the order date falls on Tuesday (DAYOFWEEK(Order Date) = 3), it means your team worked for 6 days in that week. This is because if the first day of the week is a Tuesday, it implies that the team works Tuesday through Sunday.
For all other days (Wednesday to Sunday), the ELSE part of the CASE statement assigns 7 days to the week. This assumes a standard workweek from Monday to Sunday.** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 
@MichelleH  i tried your logic, but it didnt work the way i wanted.
Here is the ideal scenario 
#this is a sample.
work can be 567 days0 
@MichelleH yup, i tried that, but still wasnt getting the right answer
0 
Its hsould show the same average for that week  if i change the timeline to This year  by Week  it should show the sum and avg by week
0 
@shreeyab The issue is because you are grouping your data by Day, so your calculation only includes orders from that particular day. Just so I understand your requirements, what should a weekly average by day represent? Is this the week to date average, rolling 7 day average, or overall average (including future dates)?
1 
overall week average @MichelleH
0 
@shreeyab if we zoom on only the row you highlighted, if I'm understanding properly, and assuming your
Orders
number is right (I just find it odd it has decimals), are you expecting to see the following?If this is the case, then you'll need to use the FIXED function, so that each day can look at more than the values for it, depending on whether your card will always be limited to a single week or not, the formula will require a BY clause or not (it requires it if you might end up showing multiple weeks in the same card). If a single week is shown (as filtered by the date range), something like this should work.
SUM(SUM(`Orders`) FIXED(BY `AssignedTo`))/AVG(COUNT(DISTINCT `ProductionScheduleDate`) FIXED(BY `AssignedTo`))
If it can show multiple weeks and you need the weekly average:
SUM(SUM(`Orders`) FIXED(BY `AssignedTo`, `ProductionScheduleDate`  WEEKDAY(`ProductionScheduleDate`)))/AVG(COUNT(DISTINCT `ProductionScheduleDate`) FIXED(BY `AssignedTo`, `ProductionScheduleDate`  WEEKDAY(`ProductionScheduleDate`)))
Whether the denominator needs to have the BY clause on the
AssignedTo
depends on whether for example in the case ofDallas
in your example it should be divided by 4 or 5. Here's an example using the second formula which is more flexible.0 
@marcel_luthi  Its not exactly orders  but its a different metric. I used Orders to make it easier to understand.
Your image is exactly what I am looking for!
Based on your explanation  I will have to take the second approach and try to see if that works.
0 
@marcel_luthi  Thank you again, it worked.!
1
Categories
 All Categories
 1.5K Product Ideas
 1.5K Ideas Exchange
 1.4K Connect
 1.1K Connectors
 284 Workbench
 4 Cloud Amplifier
 4 Federated
 2.7K Transform
 90 SQL DataFlows
 570 Datasets
 2K Magic ETL
 3.4K Visualize
 2.4K Charting
 605 Beast Mode
 18 App Studio
 29 Variables
 602 Automate
 152 Apps
 419 APIs & Domo Developer
 29 Workflows
 2 DomoAI
 28 Predict
 12 Jupyter Workspaces
 16 R & Python Tiles
 366 Distribute
 100 Domo Everywhere
 264 Scheduled Reports
 2 Software Integrations
 98 Manage
 95 Governance & Security
 15 Product Releases
 Community Forums
 37 Getting Started
 28 Community Member Introductions
 90 Community Announcements
 4.8K Archive