Forum Discussion
Counting data based off matching data and dates.
Basically..
I have 2 data sets.
1 is what our factory plans to build. It's what we've scheduled.
The other data set is what we actually built.
What I'm attempting to measure is 'On 8/19, we promised that we would build X, Y, Z on 8/22/19.'
How can I get a count of units we correctly predicted we would build and finished them, and the axis needs to be 'download date'
First table is the Scheduled Table.
Second table is what we built. We're attempting to count it based off the match with STRT_STGDT
| Date | Download Timestamp | IDENT | Kits_Required | Line | PRODUCTION_SCHEDULER | Rolling | SERIAL | SERIAL_ARRAGEMENT | SOL_PREV | SOL_SEQ_CHECK | Scheduled Date | TM_TC |
| 9/11/2019 0:00 | 8/19/2019 23:00 | 1QT00239 | 631 | Employee Name | 5296729 | 8/20/2019 0:00 | 8/20/2019 0:00 | |||||
| 9/12/2019 0:00 | 8/19/2019 23:00 | 1QT00240 | 631 | Employee Name | 5296729 | 8/20/2019 0:00 | 8/20/2019 0:00 | |||||
| 9/12/2019 0:00 | 8/19/2019 23:00 | 1QT00241 | 631 | Employee Name | 5296729 | 8/20/2019 0:00 | 8/20/2019 0:00 | |||||
| 9/12/2019 0:00 | 8/19/2019 23:00 | 1QT00242 | 631 | Employee Name | 5296729 | 8/20/2019 0:00 | 8/20/2019 0:00 | |||||
| 7/17/2019 0:00 | 8/19/2019 23:00 | 5QH00380 | 481 | Employee Name | 3570702 | 8/20/2019 0:00 | 8/20/2019 0:00 | TC | ||||
| 7/18/2019 0:00 | 8/19/2019 23:00 | 5QH00381 | 481 | Employee Name | 3570702 | 8/20/2019 0:00 | 8/20/2019 0:00 | TC | ||||
| 8/15/2019 0:00 | 8/19/2019 23:00 | 5RT03611 | 482 | Employee Name | 1512711 | 8/20/2019 0:00 | 8/20/2019 0:00 | TC | ||||
| 8/16/2019 0:00 | 8/19/2019 23:00 | 5RT03612 | 482 | Employee Name | 1512711 | 8/20/2019 0:00 | 8/20/2019 0:00 | TC | ||||
| 8/17/2019 0:00 | 8/19/2019 23:00 | 5RT03613 | 482 | Employee Name | 1512711 | 8/20/2019 0:00 | 8/20/2019 0:00 | TC | ||||
| 8/20/2019 0:00 | 8/19/2019 23:00 | 5RT03614 | 482 | Employee Name | 1512711 | 8/20/2019 0:00 | 8/20/2019 0:00 | TC | ||||
| 8/21/2019 0:00 | 8/19/2019 23:00 | 5RT03615 | 482 | Employee Name | 1512711 | 8/20/2019 0:00 | 8/20/2019 0:00 | TC | ||||
| 8/22/2019 0:00 | 8/19/2019 23:00 | 5RT03616 | 482 | Employee Name | 1512711 | 8/20/2019 0:00 | 8/20/2019 0:00 | TC | ||||
| 8/14/2019 0:00 | 8/19/2019 23:00 | 60T15639 | 479 | Employee Name | 1T1065 | 8/20/2019 0:00 | 8/20/2019 0:00 | TC | ||||
| 8/15/2019 0:00 | 8/19/2019 23:00 | 60T15640 | 479 | Employee Name | 1T1065 | 8/20/2019 0:00 | 8/20/2019 0:00 | TC | ||||
| 8/16/2019 0:00 | 8/19/2019 23:00 | 60T15641 | 479 | Employee Name | 1T1065 | 8/20/2019 0:00 | 8/20/2019 0:00 | TC | ||||
| 8/17/2019 0:00 | 8/19/2019 23:00 | 60T15642 | 479 | Employee Name | 1T1065 | 8/20/2019 0:00 | 8/20/2019 0:00 | TC | ||||
| 8/20/2019 0:00 | 8/19/2019 23:00 | 60T15643 | 479 | Employee Name | 1T1065 | 8/20/2019 0:00 | 8/20/2019 0:00 | TC | ||||
| 8/21/2019 0:00 | 8/19/2019 23:00 | 60T15644 | 479 | Employee Name | 1T1065 | 8/20/2019 0:00 | 8/20/2019 0:00 | TC | ||||
| 8/22/2019 0:00 | 8/19/2019 23:00 | 60T15645 | 479 | Employee Name | 1T1065 | 8/20/2019 0:00 | 8/20/2019 0:00 | TC | ||||
| 8/23/2019 0:00 | 8/19/2019 23:00 | 60T15646 | 479 | Employee Name | 1T1065 | 8/20/2019 0:00 | 8/20/2019 0:00 | TC | ||||
| 8/24/2019 0:00 | 8/19/2019 23:00 | 60T15647 | 479 | Employee Name | 1T1065 | 8/20/2019 0:00 | 8/20/2019 0:00 | TC | ||||
| 9/3/2019 0:00 | 8/19/2019 23:00 | 60T15648 | 479 | SHAWN RILEY | 1T1065 | 8/20/2019 0:00 | 8/20/2019 0:00 | TC | ||||
| 7/2/2019 0:00 | 8/19/2019 23:00 | B4Y00826 | REQUIRED | 109 | Employee Name | Rolled | 4200615 | 8/19/2019 0:00 | 8/20/2019 0:00 | TM | ||
| 8/7/2019 0:00 | 8/19/2019 23:00 | ERJ00857 | 479 | Employee Name | 4723136 | 8/20/2019 0:00 | 8/20/2019 0:00 | TC | ||||
| 8/7/2019 0:00 | 8/19/2019 23:00 | ERJ00858 | 479 | Employee Name | 4723136 | 8/20/2019 0:00 | 8/20/2019 0:00 | TC | ||||
| 8/7/2019 0:00 | 8/19/2019 23:00 | ERJ00859 | 479 | Employee Name | 4723136 | 8/20/2019 0:00 | 8/20/2019 0:00 | TC |
| IDENT | SERIAL_NBR | SCHD_DUE_DT | STRT_STG_NBR_CD | STRT_STG_LIT_CD | STRT_STG_DT | BLT_STG_NBR_CD | BLT_STG_LIT_CD | BLT_STG_DT | LTS_STG_NBR_CD |
| 4200615 | B4Y00826 | 7/8/2019 | 03 | SOL | 8/20/2019 | 45 | BLT | 8/20/2019 | NO |
| 4723136 | ERJ00857 | 8/13/2019 | 03 | SOL | 8/20/2019 | 45 | BLT | 12/31/9999 | NO |
| 4723136 | ERJ00858 | 8/13/2019 | 03 | SOL | 8/20/2019 | 45 | BLT | 12/31/9999 | NO |
| 4723136 | ERJ00859 | 8/13/2019 | 03 | SOL | 8/20/2019 | 45 | BLT | 12/31/9999 | NO |
| 4723136 | ERJ00860 | 8/13/2019 | 03 | SOL | 8/20/2019 | 45 | BLT | 12/31/9999 | NO |
| 4723136 | ERJ00861 | 8/13/2019 | 03 | SOL | 8/20/2019 | 45 | BLT | 12/31/9999 | NO |
| 4723136 | ERJ00862 | 8/13/2019 | 03 | SOL | 8/20/2019 | 45 | BLT | 12/31/9999 | NO |
| 4723136 | ERJ00863 | 8/13/2019 | 03 | SOL | 8/20/2019 | 45 | BLT | 12/31/9999 | NO |
| 5296729 | 1QT00239 | 9/17/2019 | 03 | SOL | 8/20/2019 | 45 | BLT | 8/20/2019 | NO |
| 5296729 | 1QT00240 | 9/18/2019 | 03 | SOL | 8/20/2019 | 45 | BLT | 8/20/2019 | NO |
| 5296729 | 1QT00241 | 9/18/2019 | 03 | SOL | 8/20/2019 | 45 | BLT | 8/20/2019 | NO |
| 5296729 | 1QT00242 | 9/18/2019 | 03 | SOL | 8/20/2019 | 45 | BLT | 8/20/2019 | NO |
3 Replies
Hi Anonymous ,
I think it is hard to achieve your requirement to use download datetime as axis, there are too many different records with same download datetime.
For your requirement, you need to add variable to store table and do looping calculation on its records to calculation through original table to find out specific date records and summary them.
After these steps, you can use iteration functions on above variable table to apply second aggregations on their result.
Can you please explain more about how to calculate your records?(e.g. category fields, filter conditions, detail rolling range...)
Regards,
Xiaoxin Sheng
Sure thing! Anonymous .
Here's my thought process.
I would add a days_forward slider.
Days Forward = DATEDIFF(RunningTotalStartOnLineSchedule[Download Timestamp],RunningTotalStartOnLineSchedule[Scheduled Date],DAY)
This is to return the days we predicted forward. I.e. "I downloaded this data on 8/27/2019. Download Timestamp is going to be the day they said they would do it. I.e. You told me on 8/27/2019(Download Timestamp) you would build Unit X, Y, Z on 8/30/2019(Scheduled Date)"
This will allow us to say 'How accurate are we when we try to schedule 3 days out, or 2 days out, or 1 day out?'
Next my thought process for measuring the adherence is below. I can think of how to do it logically in excel. Maybe it would be return a '1' if true.
'Countif RunningTotalStartOnLineSchedule[IDENT], matches a serial number in SER_STG_CRNT[SERIAL NUMBER] AND if RunningTotalStartOnLineSchedule[Schedule Date], matches the SER_STG_CRNT[STRT_STG_DT]'
This is to give me the 'We accurately scheduled (Sum of column) number of units'
Then I was thinking I could divide the Above statement, by Count of RunningTotalSTartOnLineSchedule[Schedule Date]. How many we predicted to get.
The big thing is to make sure we are only counting the explicit units we predicted. Currently we're measuring 'We said we'd build 4 units, and we built 3.' Regardless of if those 3 are any of the original 4.