4 Replies Latest reply on Sep 12, 2016 3:58 PM by stephen.ta.0

# Sum per Weekday and Max value

Hi I am looking at order data through Monday-Friday.  I am using built in tableau functions to change my "date" to weekday.

I want to know the MAX value of orders based on one years worth for each day Monday-Friday.  My issue is when I am trying to do this it is only able to pull the MAX value of a specific order on that day not the whole SUM of orders.

How do I combine SUM/MAX to work properly for this?

• ###### 1. Re: Sum per Weekday and Max value

Hi, so you are looking for the highest Monday, the highest Tuesday, etc?

What you could do is do the sum as a lod calc then take the max of that within the table.

I made three "date" fields [year], [week], and [weekday]...

DATENAME('year', [Date])

DATENAME('week', [Date])

DATENAME('weekday', [Date])

Then this calculated field to sum for the day...

{ FIXED [year], [week], [day of week] : sum([Order]) }

Then you can take the max of that within the table.

If I'm understanding your problem this should do the trick. See the attached workbook.

Hopefully this helps.

• ###### 2. Re: Sum per Weekday and Max value

Hi Chris,

That is exactly what I wanted!  Could you explain to me why I needed to break out the date into 3 fields?

Thanks!

• ###### 3. Re: Sum per Weekday and Max value

You actually don't have to break it into 3.

If there aren't times associated with your dates your fixed calc could actually just be...

{ FIXED [Date] : sum([Order]) }

If there are times you still don't need all three, you could just...

create a field [trunc_date] .... DATETRUNC('day', [Date]) ... and do

{ FIXED [trunc_date] : sum([Order]) }

• ###### 4. Re: Sum per Weekday and Max value

Hi Chris - What if I wanted to now look at again Monday-Friday, total order value per day and look at the "count" how many times an order exceeded "X" value.

thanks