
1. Re: Calculation Needed to find number of orders received by the 10th
Joe Oppelt Jun 18, 2018 9:10 AM (in response to Heather Jewell)To share sensitive data, anonymize your data.
Video demonstrates how to anonymize your workbook/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 "customer1", "customer2", 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
Deepak Rai Jun 18, 2018 10:34 AM (in response to Heather Jewell)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

upto 10th.twbx 1.2 MB


3. Re: Calculation Needed to find number of orders received by the 10th
Heather Jewell Jun 20, 2018 8:36 AM (in response to Joe Oppelt)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.

Forum Workbook.twbx 42.9 KB


4. Re: Calculation Needed to find number of orders received by the 10th
Heather Jewell Jun 20, 2018 7:06 AM (in response to Deepak Rai)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
Joe Oppelt Jun 20, 2018 8:18 AM (in response to Heather Jewell)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
Joe Oppelt Jun 20, 2018 8:20 AM (in response to Joe Oppelt)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
Heather Jewell Jun 20, 2018 8:29 AM (in response to Joe Oppelt)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.

9. Re: Calculation Needed to find number of orders received by the 10th
Joe Oppelt Jun 20, 2018 8:41 AM (in response to Heather Jewell)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
Heather Jewell Jun 20, 2018 8:50 AM (in response to Joe Oppelt)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
Joe Oppelt Jun 20, 2018 9:47 AM (in response to Heather Jewell)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
Heather Jewell Jun 20, 2018 10:26 AM (in response to Joe Oppelt)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
Joe Oppelt Jun 20, 2018 10:33 AM (in response to Heather Jewell)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 MYofPlayCycle 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!

Forum Workbook A.twbx 64.5 KB
