3 Replies Latest reply: Feb 5, 2016 9:05 AM by Ron Payne RSS

    Creating data on the row above (fiscal calendar)

    Ron Payne

      Hi All,

       

      I'm working on a fiscal calendar and can't seem to force the row above my current Fiscal Quarter flag to equal 1 (for a prior Fisc. Quarter flag). I have distinctly loaded Fisc. Year, Fisc. Quarter, and their respective current flags:

       

       

      NoConcatenate

      FiscCalPrevQuarter:

      LOAD Distinct

      FiscalYear,

      FiscalQuarter,

      cFYr_Flag,

      cFQ_Flag

      //if(cFQ_Flag = 1 and Previous(cFQ_Flag) = 0, 1, 0) as pFQFlag    //<< garbage

      Resident FiscCalFlags

      Order by FiscalYear, FiscalQuarter asc

      ;

       

       

      DROP Table FiscCalFlags;

       

       

      I want my table to look like the attached image, with the pFQ flag marking '1' where the prior fiscal quarter was. This way, i can left join it back in the full calendar and mark all of the corresponding dates for that prior quarter to be flagged with a '1' as well.

       

       

      I've attached a qvw with the quarters and flags. I've tried a number of things with peek() and previous() and can't seem to get this. I just want a 1 in a pFQ_Flag field at the row above the cFQ_Flag field (currently for this case, 2015 Q4 = 1).

       

       

       

       

      Thanks a lot,