5 Replies Latest reply: Dec 29, 2014 10:36 AM by Hasham Khan RSS

    Difference in quarters between 2 dates

    Hasham Khan

      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
          Manish Kachhia

          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
              Hasham Khan

              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

              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
                Marco Wedel

                Hi,

                 

                one solution could be:

                 

                QlikCommunity_Thread_146966_Pic1.JPG

                 

                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