5 Replies Latest reply: Aug 22, 2016 9:15 AM by Yassine El Mallahi RSS

    How compare two quarter in different years?


      I want to know how to compare two quarter in different years in a Chart. For example If I select Q2 and select year 2010, I want to compared it with the previews year Q2 of 2009, I have try but no luck I will appreciate your

      help Thanks


      This is how I read the calendar (Example)



      LOAD TempDate AS DATE,

      Year(TempDate) AS Year,

      Month(TempDate) AS Month,

      Day(TempDate) AS Day,

      Weekday(TempDate) AS WeekDay,
      'Q' &
      ceil(month(TempDate) / 3) AS Quarter,

      Week(TempDate)&'-'&Year(TempDate) AS WeekYear,

        • How compare two quarter in different years?
          Stefan Wühl

          For current selection, you do something like





          Then try

          =sum({<DATE= {">=$(=quarterstart(min(DATE),-4))<$(=quarterstart(min(DATE),-3))"}, Quarter=, Year=, Month=, Day, WeekDay=, WeekYear= >} Value)


          to retrieve the sum of Values for the same Quarter one year back (complete Quarter, not quarter to date).

            • How compare two quarter in different years?

              Thanks for your help its works


              if you can explain the expression I will appreciate



                • How compare two quarter in different years?
                  Stefan Wühl

                  This part of the expression

                  {<DATE= {">=$(=quarterstart(min(DATE),-4))<$(=quarterstart(min(DATE),-3))"}, Quarter=, Year=, Month=, Day, WeekDay=, WeekYear= >}


                  is a set expression. If you are not familiar with set analysis, please have a look at the Help - set analysis, and for point in time analysis like that I found this blog quite useful:



                  What my above set expression basically does is setting a selection only in the context of the aggregation expression (i.e. sum( Value) ). I clear all selections in the calendar fields using just the field name and equal sign, like Quarter= , and for field DATE, I set a new list of values that I determine by use of a search expression:




                  Here, the dollar sign expansion (another important piece, please look also for dollar sign expansion into the Help) will be evaluated first:

                  For example, if you select todays quarter Q1 and year 2012, min(DATE) will be 2012-01-01 or its numerical representation. Quarterstart with second parameter -4 resp. -3 will return the start of quarter 4 quarters back resp. 3 quarters back. So the search looks like this after dollar sign expansion (depending on your date format settings):




                  So we look for all DATEs that are larger equal Jan 1st 2011 and smaller than April 1st 2011, which should be ok for this quarter one year back.


                  Hope this helps,