6 Replies Latest reply on Sep 27, 2016 3:50 PM by Santiago Sanchez

# Sum of Orders and a Count of Order Values by day that exceeded "X"

Hi I want to calculate the total order value per day Monday-Friday.

Out of the 250+ business days in a given year I want to see how many times a a days order exceeded "X" value and then see whether that occurred on Monday or Tuesday and so on.

In summary:

Sum order value (regardless of order ID just by day) which I will view by Weekday via typical date functions in tableau.

Combine that with a condition to show me how many times within the year an order exceeded "X" value and show the Count for when it occurred Monday-Friday.

Thanks

• ###### 1. Re: Sum of Orders and a Count of Order Values by day that exceeded "X"

You could use an LOD expression that returns the Sum of Order Value by Day: { FIXED [Order Date] : SUM([Sales]) }.

If you build on top of it, you could count the distinct number of dates where that amount exceeded a given parameter:

COUNTD(

IF { FIXED [Order Date] : SUM([Sales]) } > [Pick X Value:]

THEN [Order Date]

END

)

An example is attached using SuperStore.

• ###### 2. Re: Sum of Orders and a Count of Order Values by day that exceeded "X"

Attached is another workbook using similar logic to what Santiago posted.  I do the counts slightly different but the effect should be the same.

• ###### 3. Re: Sum of Orders and a Count of Order Values by day that exceeded "X"

Hi Santiago, So I am trying to do the reverse but instead of order value I wanna use return value.  In theory its simply just swapping return value in but it is not working.

Am I interpreting something wrong?

• ###### 4. Re: Sum of Orders and a Count of Order Values by day that exceeded "X"

I'm not sure how you're data is structured but that sounds right, replacing [Sales] with [ReturnedSales] for instance. Maybe you also need to change [Order Date] with [Returned Date]?

Again, might depend on your data...

• ###### 5. Re: Sum of Orders and a Count of Order Values by day that exceeded "X"

Still haven't figured this out and the data is structured the same way order values are.  Just wondering if I add a reference line can I use that count function to count anything under that reference line ( which is set as a parameter?)

• ###### 6. Re: Sum of Orders and a Count of Order Values by day that exceeded "X"

Can you attach a twbx with some sample/not-real data and this behavior? We can try to figure it out from there.