Average question

shreeb

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?

marcel_luthi

@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 of Dallas in your example it should be divided by 4 or 5. Here's an example using the second formula which is more flexible.

MichelleH

@shreeyab This largely depends on how your data is structured, but assuming you have one row per order you can use a beast mode like this and group by week:

count(`Order`)/count(distinct `Order Date`)

ArborRose

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.

shreeb

@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 5-6-7- days

MichelleH

@shreeyab If your data is already grouped by day, then you can change the numerator to SUM(`Orders`)

shreeb

@MichelleH yup, i tried that, but still wasnt getting the right answer

MichelleH

@shreeyab Can you please show some screenshots or examples of the incorrect results?

shreeb

@MichelleH -

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

MichelleH

@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)?

shreeb

overall week average @MichelleH

marcel_luthi

@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 of Dallas in your example it should be divided by 4 or 5. Here's an example using the second formula which is more flexible.

shreeb

@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.

shreeb

@marcel_luthi - Thank you again, it worked.!

shreeb

@marcel_luthi i have to update this logic and im not sure how.

shreeb

SUM(SUM(`Orders`) FIXED(BY `AssignedTo`, `ProductionScheduleDate` - WEEKDAY(`ProductionScheduleDate`)))/AVG(COUNT(DISTINCT `ProductionScheduleDate`) FIXED(BY `AssignedTo`, `ProductionScheduleDate` - WEEKDAY(`ProductionScheduleDate`)))

This solution worked but business now wants to change this to show average of 5 days and exclude holidays