community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

Spliting data by date range

Highlighted

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.QuantityReceipt DateDate Of Payment
1262015-10-212017-03-27
2232009-11-012017-02-03
32532016-11-292017-06-02

 

Following is the table with rates:

 

Date StartDate EndRate 
April 1, 2019June 30, 20190.06Q2,2019
January 1, 2019 March 31, 20190.06Q1,2019
October 1, 2018 December 31, 20180.05Q4,2018
July 1, 2018 September 30, 20180.05Q3,2018
April 1, 2018 June 30, 20180.05Q2,2018
January 1, 2018 March 31, 20180.04Q1,2018
October 1, 2017 December 31, 20170.04Q4,2017
July 1, 2017 September 30, 20170.04Q3,2017
April 1, 2017 June 30, 20170.04Q2,2017
January 1, 2017 March 31, 20170.04Q1,2017
October 1, 2016 December 31, 20160.04Q4,2016
July 1, 2016 September 30, 20160.04Q3,2016
April 1, 2016 June 30, 20160.04Q2,2016
January 1, 2016 March 31, 20160.03Q1,2016
October 1, 2015 December 31, 20150.03Q4,2015
July 1, 2015 September 30, 20150.03Q3,2015
April 1, 2015 June 30, 20150.03Q2,2015
January 1, 2015 March 31, 20150.03Q1,2015
October 1, 2014 December 31, 20140.03Q4,2014
July 1, 2014 September 30, 20140.03Q3,2014
April 1, 2014 June 30, 20140.03Q2,2014
January 1, 2014 March 31, 20140.03Q1,2014
October 1, 2013 December 31, 20130.03Q4,2013
July 1, 2013 September 30, 20130.03Q3,2013
April 1, 2013 June 30, 20130.03Q2,2013
January 1, 2013 March 31, 20130.03Q1,2013
October 1, 2012 December 31, 20120.03Q4,2012
July 1, 2012 September 30, 20120.03Q3,2012
April 1, 2012 June 30, 20120.03Q2,2012
January 1, 2012 March 31, 20120.03Q1,2012
October 1, 2011 December 31, 20110.03Q4,2011
July 1, 2011 September 30, 20110.04Q3,2011
April 1, 2011 June 30, 20110.04Q2,2011
January 1, 2011 March 31, 20110.03Q1,2011
October 1, 2010 December 31, 20100.04Q4,2010
July 1, 2010 September 30, 20100.04Q3,2010
April 1, 2010 June 30, 20100.04Q2,2010
January 1, 2010 March 31, 20100.04Q1,2010
October 1, 2009 December 31, 20090.04Q4,2009
July 1, 2009 September 30, 20090.04Q3,2009
April 1, 2009 June 30, 20090.04Q2,2009
January 1, 2009 March 31, 20090.05Q1,2009
October 1, 2008 December 31, 20080.06Q4,2008
July 1, 2008 September 30, 20080.05Q3,2008
April 1, 2008 June 30, 20080.06Q2,2008
January 1, 2008 March 31, 20080.07Q1,2008
October 1, 2007 December 31, 20070.08Q4,2007
July 1, 2007 September 30, 20070.08Q3,2007
April 1, 2007 June 30, 20070.08Q2,2007
January 1, 2007 March 31, 20070.08Q1,2007
October 1, 2006 December 31. 20060.08Q4,2006
July 1, 2006 September 30, 20060.08Q3,2006
April 1, 2006 June 30, 20060.07Q2,2006
January 1, 2006 March 31, 20060.07Q1,2006
October 1, 2005 December 31, 20050.07Q4,2005
July 1, 2005 September 30, 20050.06Q3,2005
April 1, 2005 June 30, 20050.06Q2,2005
Magnetar

Hi @shehryarazhar 

 

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.

Magnetar

Hi @shehryarazhar 

 

Here's my first draft of the workflow.  

 

WF.png

 

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

 

Results.png

 

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.

Magnetar

You're welcome @shehryarazhar 

 

Glad to help

 

Dan

Labels