1 Reply Latest reply: Oct 20, 2015 8:56 AM by Marcus Sommer RSS

    Last quarter

    Kenny Hua

      Hello,

       

      I need to make appear the last quarter from my selection:

       

      QuarterAndCumul:

      LOAD Year,

           information1,

           information2,

           information3,

           information4,

        Date(MakeDate(Left(Year,4),(Right(Year,1)-1)*3+1,1)) as Quarter

      FROM

      QnC.qvd

      (qvd);

       

      ==================================================

       

      Year
      2000_Q1
      2000_Q2
      2001_Q3
      2001_Q4

       

      I desire when I select "2000_Q2" a table show 2000_Q1's informations and 2001_Q4 with the same table 2001_Q3's information

       

      Thanks for your help

       

      Regards

        • Re: Last quarter
          Marcus Sommer

          I think I would try it with an additionally numeric year-field (here are two versions) and set analysis:

           

          Load

          ...

          // left(Year,4) * 10 + right(Year,1) as YearNum, // will be more complicated because it's not linear

          if(rowno() = 1, 1, if(Year = peek('Year'), peek('YearNUM'), peek('YearNUM') + 1)) as YearNum,

          ...

           

          and as expression something like:

           

          sum({< YearNum = {">=$(=max(YearNum) - 1)<$(=max(YearNum) + 2)"}>} Information)

           

          The bolded parts might be adjusted to your needs.

           

          - Marcus