13 Replies Latest reply on Jun 20, 2018 10:33 AM by Joe Oppelt

Calculation Needed to find number of orders received by the 10th

Hello,

I have a bit of a tough calculation I'm trying to come up with and I'm a complete beginner at calculations in Tableau.

I have a set of data, that records a date an order was submitted into our internal system. Our data is broken down by play cycles, and orders can be submitted for each play cycle 3 plus months before a given play cycle. I need to find the number of orders received from the time the first order for a given play cycle was submitted until the 10th of the month, 3 months before the play cycle date.

For example, the May 2018 play cycle cut off for submitted orders is March 10th. I need to calculate the number of orders that were submitted anywhere between January 1st to March 10th.

I had was given this calculation earlier, but it's pulling a much smaller percentage then it should, in the report.

(int(DATE(MONTH([Play Cycle]))-DATE(MONTH([Date Submitted to SAFE]))) >= 3) AND (INT(DAY([Date Submitted to SAFE]))<= 10)

I'm unable to share the data, but any help or pointers anyone could provide would be greatly appreciated.

Thank you!

Heather

• 1. Re: Calculation Needed to find number of orders received by the 10th

To share sensitive data, anonymize your data.

We really don't need all the rest of the data for this question.  Just the rows for 2 play cycles with the dates.  And maybe other dimensional values that are necessary to make the viz you need.  (And those values can be changed to "customer-1", "customer-2", etc.)

I want to help with a solution that uses whatever you use to determine the start of your counting period and end of counting period, and do it in whatever version of Tableau you are using so that you'll be able to open whatever I do.

• 2. Re: Calculation Needed to find number of orders received by the 10th

Hi Heather.

I think You are Looking for This. You Need a Parameter, Two Calculations amnd One Filter Set TO TRUE to Achieve It. Pl chreck Screenshots and attached and If It Helps, Pl mark it Helpful and CORRECt to close Thread.

Thanks

Deepak

For May 2016

FOR Aug 2016

• 3. Re: Calculation Needed to find number of orders received by the 10th

Joe Oppelt Sorry for my delay and thank you for a quick response!

I've created a test workbook with test data that represents the data I'm working with. Hopefully this is what you were looking for? I can also attach the excel sheet if that's all you need.

• 4. Re: Calculation Needed to find number of orders received by the 10th

I'm looking for a chart that looks like this

And I just need to capture the percentage of orders that were submitted before the 10th of the month, 3 months prior to the due date.

So, for example for the July 2018 play cycle, the due date to have orders submitted is the 10th of April. However, orders could've been submitted anytime from January 2018 to the deadline of April 10th. I need to calculate the percentage of the total orders for a play cycle that were submitted by the 10th.

Thank you!

• 5. Re: Calculation Needed to find number of orders received by the 10th

I have this open, but I don't see what I need.  You said:

Heather Jewell wrote:

...

For example, the May 2018 play cycle cut off for submitted orders is March 10th. I need to calculate the number of orders that were submitted anywhere between January 1st to March 10th.

How do I know the May 2018 cutoff date from the data?

• 6. Re: Calculation Needed to find number of orders received by the 10th

And in your screen shot you show [Company].  Do I use [Airline] for that in the supplied workbook?

• 7. Re: Calculation Needed to find number of orders received by the 10th

Apologies, I forgot to change the title of each column. "PO Originator" is company.

And to answer your first question, the May cut off date is 3 months prior to the month. So for May, it would be February 10th. Nothing in the data shows that at this time. Would it be easier to calculate if we entered that date into the database? Right now, that data isn't collected but could be manually calculated and entered.

Also, my requirements have changed a bit, instead of making the cut off the 10th of the month, we'd like to make it 14 days before the due date. The due date is different for all clients.

• 8. Re: Calculation Needed to find number of orders received by the 10th

Joe Oppelt

I've had to update the workbook, that's posted. Just an FYI. Thanks!

• 9. Re: Calculation Needed to find number of orders received by the 10th

What field is "Due Date"?

I see OEM Due Date, but for each company there are multiple values in this field.

• 10. Re: Calculation Needed to find number of orders received by the 10th

yes OEM due date is the correct field to use for Due Date. Each client has multiple due dates that we're working with.

• 11. Re: Calculation Needed to find number of orders received by the 10th

Further clarification needed.  You need to spell out what you're really looking for.  I don't know your business or your data.

So for each PO Originator, there are multiple due dates.  I assume therefore, that there are multiple rows with that due date.  I need to compare one of those other dates to the due date, counting those rows where the date in question is up to 14 days before the Due date.  Am I looking at [Date Submitted to SAFE] for that?

And you want this by company, not by Due Date, correct?  So if Company 1 has 9 due dates, and 7 of them are for the May2018 Play Cycle, among those 7 due dates there might be 100 submission rows, and 40 of them are before each submission's due date.  So the May 2018 value for company 1 would be 40%.

Is that what you're looking for?

• 12. Re: Calculation Needed to find number of orders received by the 10th

Your first question is correct, we'd be comparing the "Date Submitted to SAFE" against the "OEM Due Date."

And the second part is absolutely correct.

Sorry for not being clear enough in the beginning and thank you for taking your time to work on this!!

• 13. Re: Calculation Needed to find number of orders received by the 10th

In the attached on sheet 1 I added 3 calcs.  The first compares the date submitted (at the row level) to the Due Date minus 14 days.  (Tableau date math is cool.  Just subtracting 14 subtracts 14 days.)  I get a 1 or a zero on each row this way.  I made this calc a dimension.

Next I did a FIXED LOD calc at the [PO Originator] and MY-of-PlayCycle level to add up the number of overall rows.

And finally I did the same to add up the number of 1s in [Submitted in time].  Those three values are displayed on Sheet 1 for each cell.

(PS:  To get that MY of Play Cycle in those calcs, I just dragged the pill from the COlumns shelf into the calc editor, and Tableau expanded out the correct syntax to define that dimensional level for me.)

So now I have the numbers I need to do the percentage.

Go to Sheet 1(2).

I made one more calc to do the %.  Displayed that on the sheet, and there you go!

1 of 1 people found this helpful