6 Replies Latest reply: Aug 7, 2014 3:54 PM by Kris Balow RSS

    Selecting Latest 2 Quarters

      Hello Eveyone,

       

      I have 2 fields; Quarter which includes 1,2,3,4 and another field YEAR which includes YEAR. I want to use those 2 fields to get the data for the latest 2 quarters in charts which is 2014 Q1 and 2014 Q2 by using set analysis and this has to be dynamic, so when the new data populates for the new quarter which is 2014 Q3, the chart will show data for 2014 Q3 and 2014 Q2.

       

      Thanks.

        • Re: Selecting Latest 2 Quarters
          Jonathan Poole

          i liked to do prior period SET ANALYSIS using a numerical counter of months ( or in this case quarters) that spans years.

           

          here is an example of what you are asking

           

          the SET ANALYSIS is:

           

          $<QuarterCounter={$(vMaxQuarter),$(=vMaxQuarter-1)}>}

           

          Where QuarterCounter is the running counter and vMaxQuarter is a variable =max( {1} QuarterCounter)

           

          jonny

          • Re: Selecting Latest 2 Quarters

            Thanks for the response.

            But the max of the Quarter will always be 4. so how can this be done?

              • Re: Selecting Latest 2 Quarters
                Jonathan Poole

                In the example there is Quarter (1 , 2, 3, 4) and in the load i added a brand new QuarterCounter field that assigns (1,2,3,4,5,6 etc... ) in progression to each quarter from earliest to latest time period.

                 

                So that allows me to reference a quarter numerically across years.

                 

                as you can see in the load script you need a calendar table that is sorted from earliest to latest with all the years and quarters in order to do this.  It means adding a calendar table to the data model but i think that is a good thing and it certainly makes YTD expressions easier.

              • Re: Selecting Latest 2 Quarters

                Build a calendar table if you didn't already do that.  The Date field in your data will tie to the dates in your calendar.  You can add additional fields to your calendar for whatever you need to filter on or do date math with.  For example:

                Date, CalYear, FiscalYear, FiscalQtr, FiscalYYYYQ, FiscalEOYDate, FiscalMonth, etc.

                Most of the time business data contains future dates.    To handle this set up some macros/variables to  get the max non-future date in your filtered data.  That is better than using today() or max(date)since users may select a year range outside the current year.    You can use that in your set analysis or conditions... for example:

                 

                 

                v_MaxFiscalYear = if (Year(Today()) < max(FiscalYear),

                    Year(Today()),

                    max(FiscalYear)

                 

                 

                sum({$<FiscalYear={$(=v_MaxFiscalYear - 1)}>}  SomeField)

                • Re: Selecting Latest 2 Quarters
                  Kris Balow

                  You could use the P() function to select only the possible quarters within the respective year.  Something like this:

                   

                  max({$<Quarter = P({$<YEAR=max(YEAR)>}) Quarter>})