Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
I have 2 date field with the format DD/MM/YYYY which i have converted to yearquarter with the format 'YYYY-QQ'. I am looking to find the difference in quarters between the two date field. Here is an example:
Date1 Date2 QuarterDate1 QuarterDate2 QuarterDifference
1/1/2012 1/4/2012 2012-Q1 2012-Q2 1 (i.e. 2012Q2 - 2012Q1)
1/12/2013 1/1/2014 2013-Q4 2014-Q1 1 (i.e. 2014Q1 - 2013Q4)
any idead?
Hi,
one solution could be:
LOAD *,
(Month(QuarterDate2)-Month(QuarterDate1))/3+(Year(QuarterDate2)-Year(QuarterDate1))*4 as QuarterDifference;
LOAD *,
Dual(Year(Date1)&'-Q'&Ceil(Month(Date1)/3),QuarterStart(Date1)) as QuarterDate1,
Dual(Year(Date2)&'-Q'&Ceil(Month(Date2)/3),QuarterStart(Date2)) as QuarterDate2
INLINE [
Date1, Date2
1/1/2012, 1/4/2012
1/12/2013, 1/1/2014
1/1/2012, 1/4/2014
1/2/2012, 1/4/2014
1/3/2012, 1/4/2014
1/4/2012, 1/4/2014
1/5/2012, 1/4/2014
1/6/2012, 1/4/2014
1/7/2012, 1/4/2014
1/8/2012, 1/4/2014
1/9/2012, 1/4/2014
1/9/2012, 1/4/2014
1/10/2012, 1/4/2014
1/11/2012, 1/4/2014
1/12/2012, 1/4/2014
1/1/2013, 1/4/2014
1/2/2013, 1/4/2014
1/3/2013, 1/4/2014
1/4/2013, 1/4/2014
1/5/2013, 1/4/2014
1/6/2013, 1/4/2014
1/7/2013, 1/4/2014
1/8/2013, 1/4/2014
1/9/2013, 1/4/2014
1/10/2013, 1/4/2014
1/11/2013, 1/4/2014
1/12/2013, 1/4/2014
1/1/2014, 1/4/2014
1/2/2014, 1/4/2014
1/3/2014, 1/4/2014
1/4/2014, 1/4/2014
];
hope this helps
regards
Marco
If(Year(Date#(Date1,'D/M/YYYY')) < Year(Date#(Date2,'D/M/YYYY')),
Ceil(Month(Date#(Date2,'D/M/YYYY'))/3) + 4 - Ceil(Month(Date#(Date1,'D/M/YYYY'))/3),
Ceil(Month(Date#(Date2,'D/M/YYYY'))/3) - Ceil(Month(Date#(Date1,'D/M/YYYY'))/3),
) as Difference
Hi Manish,
Thanks for the reply. It work for the majority of the columns, however, it only outputs 4 when the the difference over the 2 date fields is over a year.
For example:
Date1 Date2 Code Answer Correct Answer
1/1/2012 1/4/2014 4 9
Also i cannot seem to get the code to work in the load script editor. If i remove the last comma the script does not have any errors, however, the calculation then does not work. Any ideas?
I would alter the calendar load script to generate a quarter sequence using autonumber(). Then store the quarters using dual() to hold the numeric sequence ant text part in a single dual field. Then you can simply subtract the two quarter fields to find the number of quarters between the dates.
Hi,
one solution could be:
LOAD *,
(Month(QuarterDate2)-Month(QuarterDate1))/3+(Year(QuarterDate2)-Year(QuarterDate1))*4 as QuarterDifference;
LOAD *,
Dual(Year(Date1)&'-Q'&Ceil(Month(Date1)/3),QuarterStart(Date1)) as QuarterDate1,
Dual(Year(Date2)&'-Q'&Ceil(Month(Date2)/3),QuarterStart(Date2)) as QuarterDate2
INLINE [
Date1, Date2
1/1/2012, 1/4/2012
1/12/2013, 1/1/2014
1/1/2012, 1/4/2014
1/2/2012, 1/4/2014
1/3/2012, 1/4/2014
1/4/2012, 1/4/2014
1/5/2012, 1/4/2014
1/6/2012, 1/4/2014
1/7/2012, 1/4/2014
1/8/2012, 1/4/2014
1/9/2012, 1/4/2014
1/9/2012, 1/4/2014
1/10/2012, 1/4/2014
1/11/2012, 1/4/2014
1/12/2012, 1/4/2014
1/1/2013, 1/4/2014
1/2/2013, 1/4/2014
1/3/2013, 1/4/2014
1/4/2013, 1/4/2014
1/5/2013, 1/4/2014
1/6/2013, 1/4/2014
1/7/2013, 1/4/2014
1/8/2013, 1/4/2014
1/9/2013, 1/4/2014
1/10/2013, 1/4/2014
1/11/2013, 1/4/2014
1/12/2013, 1/4/2014
1/1/2014, 1/4/2014
1/2/2014, 1/4/2014
1/3/2014, 1/4/2014
1/4/2014, 1/4/2014
];
hope this helps
regards
Marco
Thanks Marco. For some reason it didnt work with the field names i gave it but works with your names. As long as it works. Thanks again.