Hi Everybody,
I'm trying to figure out a way where a user can pick two dates and see the expected sales and get a walk-across for those sales for the month they've chosen.
For example, I have the data set below. As of 10/30 in the Datestamp field, the Dollars for November (using the Expected_Ship_Month field) is $850,000. However, as of 10/31, the Dollars for November are now $425,000 since job_3 had a change to the Expected_Ship_Date field and the job moved into December, and job_4 changed from 12/01 to 11/30.
What I would like the end users to be able to do is choose three variables: a Beginning Date and an End Date using the Datestamp field, and then the month (Expected_Ship_Month) for which they would like to see the sales.
The difficult part is not getting the beginning and ending sales but in trying to explain which orders got delayed or which orders shipped early.
Anyone have any ideas on how to get a formula showing what jobs got pushed out into the next month and also a separate expression to show what jobs got pulled into the month?
Thanks!
Justin
| RecNo() | Job_Number | Datestamp | Expected_Ship_Date | Expected_Ship_Month | Dollars |
| 1 | job_1 | 10/30/2014 | 11/01/2014 | 11/01/2014 | 100000 |
| 2 | job_1 | 10/31/2014 | 11/01/2014 | 11/01/2014 | 100000 |
| 3 | job_1 | 11/01/2014 | 11/01/2014 | 11/01/2014 | 100000 |
| 4 | job_1 | 11/02/2014 | 11/10/2014 | 11/01/2014 | 100000 |
| 5 | job_2 | 10/30/2014 | 11/04/2014 | 11/01/2014 | 250000 |
| 6 | job_2 | 10/31/2014 | 11/04/2014 | 11/01/2014 | 250000 |
| 7 | job_2 | 11/01/2014 | 11/04/2014 | 11/01/2014 | 250000 |
| 8 | job_2 | 11/02/2014 | 11/04/2014 | 11/01/2014 | 250000 |
| 9 | job_2 | 11/03/2014 | 11/04/2014 | 11/01/2014 | 250000 |
| 10 | job_3 | 10/29/2014 | 11/30/2014 | 11/01/2014 | 500000 |
| 11 | job_3 | 10/30/2014 | 11/30/2014 | 11/01/2014 | 500000 |
| 12 | job_3 | 10/31/2014 | 12/15/2014 | 12/01/2014 | 500000 |
| 13 | job_4 | 10/30/2014 | 12/01/2014 | 12/01/2014 | 75000 |
| 14 | job_4 | 10/31/2014 | 11/30/2014 | 11/01/2014 | 75000 |
| 15 | job_4 | 11/01/2014 | 11/30/2014 | 11/01/2014 | 75000 |
| 16 | job_4 | 11/02/2014 | 11/30/2014 | 11/01/2014 | 75000 |
