Hi,
I am looking to split each line item in a fairly large data set into annual quarters as each line item contains a date range spanning a few years.
I need to split them into quarterly data because I have quarterly rates available to me which I need subsequently multiply by the line item and sum them to get my total cost per line item.
Any help splitting the data into quarters based on the Start Date and End Date will be much appreciated.
Following is an example of the data.
Item no. | Quantity | Receipt Date | Date Of Payment |
1 | 26 | 2015-10-21 | 2017-03-27 |
2 | 23 | 2009-11-01 | 2017-02-03 |
3 | 253 | 2016-11-29 | 2017-06-02 |
Following is the table with rates:
Date Start | Date End | Rate | |
April 1, 2019 | June 30, 2019 | 0.06 | Q2,2019 |
January 1, 2019 | March 31, 2019 | 0.06 | Q1,2019 |
October 1, 2018 | December 31, 2018 | 0.05 | Q4,2018 |
July 1, 2018 | September 30, 2018 | 0.05 | Q3,2018 |
April 1, 2018 | June 30, 2018 | 0.05 | Q2,2018 |
January 1, 2018 | March 31, 2018 | 0.04 | Q1,2018 |
October 1, 2017 | December 31, 2017 | 0.04 | Q4,2017 |
July 1, 2017 | September 30, 2017 | 0.04 | Q3,2017 |
April 1, 2017 | June 30, 2017 | 0.04 | Q2,2017 |
January 1, 2017 | March 31, 2017 | 0.04 | Q1,2017 |
October 1, 2016 | December 31, 2016 | 0.04 | Q4,2016 |
July 1, 2016 | September 30, 2016 | 0.04 | Q3,2016 |
April 1, 2016 | June 30, 2016 | 0.04 | Q2,2016 |
January 1, 2016 | March 31, 2016 | 0.03 | Q1,2016 |
October 1, 2015 | December 31, 2015 | 0.03 | Q4,2015 |
July 1, 2015 | September 30, 2015 | 0.03 | Q3,2015 |
April 1, 2015 | June 30, 2015 | 0.03 | Q2,2015 |
January 1, 2015 | March 31, 2015 | 0.03 | Q1,2015 |
October 1, 2014 | December 31, 2014 | 0.03 | Q4,2014 |
July 1, 2014 | September 30, 2014 | 0.03 | Q3,2014 |
April 1, 2014 | June 30, 2014 | 0.03 | Q2,2014 |
January 1, 2014 | March 31, 2014 | 0.03 | Q1,2014 |
October 1, 2013 | December 31, 2013 | 0.03 | Q4,2013 |
July 1, 2013 | September 30, 2013 | 0.03 | Q3,2013 |
April 1, 2013 | June 30, 2013 | 0.03 | Q2,2013 |
January 1, 2013 | March 31, 2013 | 0.03 | Q1,2013 |
October 1, 2012 | December 31, 2012 | 0.03 | Q4,2012 |
July 1, 2012 | September 30, 2012 | 0.03 | Q3,2012 |
April 1, 2012 | June 30, 2012 | 0.03 | Q2,2012 |
January 1, 2012 | March 31, 2012 | 0.03 | Q1,2012 |
October 1, 2011 | December 31, 2011 | 0.03 | Q4,2011 |
July 1, 2011 | September 30, 2011 | 0.04 | Q3,2011 |
April 1, 2011 | June 30, 2011 | 0.04 | Q2,2011 |
January 1, 2011 | March 31, 2011 | 0.03 | Q1,2011 |
October 1, 2010 | December 31, 2010 | 0.04 | Q4,2010 |
July 1, 2010 | September 30, 2010 | 0.04 | Q3,2010 |
April 1, 2010 | June 30, 2010 | 0.04 | Q2,2010 |
January 1, 2010 | March 31, 2010 | 0.04 | Q1,2010 |
October 1, 2009 | December 31, 2009 | 0.04 | Q4,2009 |
July 1, 2009 | September 30, 2009 | 0.04 | Q3,2009 |
April 1, 2009 | June 30, 2009 | 0.04 | Q2,2009 |
January 1, 2009 | March 31, 2009 | 0.05 | Q1,2009 |
October 1, 2008 | December 31, 2008 | 0.06 | Q4,2008 |
July 1, 2008 | September 30, 2008 | 0.05 | Q3,2008 |
April 1, 2008 | June 30, 2008 | 0.06 | Q2,2008 |
January 1, 2008 | March 31, 2008 | 0.07 | Q1,2008 |
October 1, 2007 | December 31, 2007 | 0.08 | Q4,2007 |
July 1, 2007 | September 30, 2007 | 0.08 | Q3,2007 |
April 1, 2007 | June 30, 2007 | 0.08 | Q2,2007 |
January 1, 2007 | March 31, 2007 | 0.08 | Q1,2007 |
October 1, 2006 | December 31. 2006 | 0.08 | Q4,2006 |
July 1, 2006 | September 30, 2006 | 0.08 | Q3,2006 |
April 1, 2006 | June 30, 2006 | 0.07 | Q2,2006 |
January 1, 2006 | March 31, 2006 | 0.07 | Q1,2006 |
October 1, 2005 | December 31, 2005 | 0.07 | Q4,2005 |
July 1, 2005 | September 30, 2005 | 0.06 | Q3,2005 |
April 1, 2005 | June 30, 2005 | 0.06 | Q2,2005 |
Solved! Go to Solution.
How do you split the quantities? Does each get an equal share, or is it proportional to length of the Q record?
Dan
Hi Dan,
It will be proportional to the length of each Q.
Thanks.
Here's my first draft of the workflow.
It starts by determining which quarter the Receipt and Payment dates are in(1st 2 joins). Then It generates intermediate records, one for each quarter. After sorting you end up with this
At this point I'm not sure what you want to do. You have the rate for each quarter and the total Quantity for the item. I didn't break down the quantity by quarter because for Item 2, you have 23 items but 30 quarters(See the Quantity Information container), meaning that you'd have fractional items or some quarters with 0 items.
How do you want to proceed? Is this to the point where you can proceed with the math? If not, let me know what the final results for each quarter should be and how you calculate them and I'll add that on to the workflow. Make sure you include details of how you calculate the results for the first and last quarters of each item, since these aren't full quarters
Dan
Hi Dan,
Many thanks. That worked beautifully post some minor modifications for a sample of the data. I am fine on the calculations so just did some and checked versus manual workings I had performed and all looks good.
I am going to run the whole data set through this workflow now.
Thanks again.