topic Difference in quarters between 2 dates in QlikView

Difference in quarters between 2 dates

Anonymous — Mon, 29 Dec 2014 14:02:35 GMT

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?

Re: Difference in quarters between 2 dates

MK_QSL — Mon, 29 Dec 2014 14:22:56 GMT

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

Re: Difference in quarters between 2 dates

Anonymous — Mon, 29 Dec 2014 14:41:10 GMT

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?

Re: Difference in quarters between 2 dates

Colin-Albert — Mon, 29 Dec 2014 14:49:28 GMT

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.

Re: Difference in quarters between 2 dates

MarcoWedel — Mon, 29 Dec 2014 15:04:54 GMT

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

Re: Difference in quarters between 2 dates

Anonymous — Mon, 29 Dec 2014 15:36:09 GMT

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.