1 2 Previous Next 20 Replies Latest reply on Nov 12, 2018 10:04 AM by adam jama

# Average spent in a specific weekday

Hello,

Im trying to figure out how much is spent on average for a typical monday, tuesday, wednesday etc... based on this worksheet Tableau Public (amount spent total per days)

When I do the average of the subtotal i get the average amount of a typical order that day. What Im looking for is the average amount for a typical monday, tuesday etc.

Thanks in advance for any help.

• ###### 1. Re: Average spent in a specific weekday

Use this calculation to get Average per Weekday.

• ###### 2. Re: Average spent in a specific weekday

This is giving me the average order in general. not the average amount spent on a specified weekday.

• ###### 3. Re: Average spent in a specific weekday

You can use an exclude LOD for this:

{EXCLUDE [Order Time]: Avg([Subtotal])}

That will give you the average for a given day across all of its ordering times per restaurant.

If you don't want the restaurant considered in there either then use this:

{EXCLUDE [Restaurant], [Order Time]: Avg([Subtotal])}

Best,

Paul

• ###### 4. Re: Average spent in a specific weekday

May be you are looking for this

If You want Per Restaurant Include Restaurant Dimension in this:

{FIXED [Weekday]:AVG([Subtotal])}

Like this

{FIXED Restaurent, [Weekday]:AVG([Subtotal])}

Thanks

Deepak

• ###### 5. Re: Average spent in a specific weekday

Hi Paul,

Thanks for taking the time to reply.

When I use your calculation its giving me the same numbers did I have which is the average amount of an order that day. not the average of the whole day.

Thanks!

• ###### 6. Re: Average spent in a specific weekday

Hey Adam - I'm a little confused on how you want this calculated.

Right now your table lists Restaurants, Weekdays, order times, and subtotals for that order time period.

What do you mean by "the average of the whole day"? You need to average the subtotal by something during that day.  Can you help me by telling me what you want your average calculation to do

do you want this:

sum of all subtotals for a given day divided by number of orders (doesn't sound like you want this though)

or something like this:

sum of all subtotals for a given day divided by the number of restaurants?

If you let me know that then I can help you figure out the calculation.  Right now I'm not quite understanding how you want it.

Best,

Paul

1 of 1 people found this helpful
• ###### 7. Re: Average spent in a specific weekday

Hi Paul!

On a typical weekday let's say a tuesday, the average sales that we do is around \$1,000 so Im trying to figure out how to get that amount.

When I calculate the average currently its giving me something around \$13-\$15 which represents the average amount of a single order.

Based on the worksheet id like to know on all the tuesdays of data what is the average sales for a tuesday.

• ###### 8. Re: Average spent in a specific weekday

Oh ok cool, I think I get it now.  You want the average for a given day - which is the sum of the subtotal for that day divided by the number of that given day of the week appears in the dataset.

Try this (change the Order Date field to whatever it's called in your data):

{fixed DATEPART('weekday', [Order Date]):sum([Sales])}

/

{fixed DATEPART('weekday', [Order Date]): COUNTD([Order Date])}

Best,

Paul

• ###### 9. Re: Average spent in a specific weekday

Hey Paul,

I tried typing the calculation and below is the error appearing. Could you tell me what im doing wrong?

Thanks

• ###### 10. Re: Average spent in a specific weekday

That's easy to fix. The Weekday part of the Calc is there to tell the datepart function which part of the date to return. You just want to include the word weekday between single quotes like this:

'weekday'

Fix that in both parts of the calc and let me know how that goes.

Paul

• ###### 11. Re: Average spent in a specific weekday

Hey Paul,

Here's what im getting now:

• ###### 12. Re: Average spent in a specific weekday

Hey Paul,

Dismiss my previous message I was able to enter the calculation.

However, the numbers Im getting now are still in the 2 digits range, our average on a typical weekday should be around \$1000

Any guess whats going on?

Thanks!

• ###### 13. Re: Average spent in a specific weekday

Can you share that file?  It'd be easier for me to troubleshoot if I can work on it directly.

I didn't realize that you wanted to figure this out at the restaurant level - I thought you wanted days overall regardless of restaurant.  I'm not 100% sure if that's throwing it off but you can try this slightly updated calc that includes the Restaurant field too:

{fixed [Restaurant], DATEPART('weekday', [Order Date]):sum([Sales])}

/

{fixed [Restaurant], DATEPART('weekday', [Order Date]): COUNTD([Order Date])}

1 of 1 people found this helpful
• ###### 14. Re: Average spent in a specific weekday

Here's the link to the workbook on tableau public Tableau Public I believe you can download the worksheet from there, im not able to add it to this reply.

I tried the new calculation but it still is giving an amount that looks like an average order instead of an average for a typical weekday.

Thanks!