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

    How compare two quarter in different years?

      Hi

      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)

       

      MasterCalendar:

      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

          =sum(Value)

           

          ?

           

          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

               

              Thanks,

                • 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:

                  http://iqlik.wordpress.com/2010/11/27/the-magic-of-set-analysis-point-in-time-reporting/

                   

                  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:

                   

                  ">=$(=quarterstart(min(DATE),-4))<$(=quarterstart(min(DATE),-3))"

                   

                  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):

                   

                  ">=2011-01-01<2011-04-01"

                   

                  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,

                  Stefan