1 Reply Latest reply on Jan 18, 2016 8:18 PM by Tableau kumar

    Add Prior Year column to Table - Possibly using Full Outer Join

    Randy Davis

      I am trying to compare 2016 budget amounts to 2015 actual amounts by general ledger (G/L) account, Department and Period.  However, we do not budget by every G/L account, so if I query all of the rows in 2016, I'd like to append to the query results Dept-Account combinations that existed in 2015, but which we don't have budgeted in 2016.

      I thought I could join the original table to Custom SQL results (see SQL below) of the prior year; where I added a field (Next_Year_Period_ID = PERIOD_ID + 12) that is used to join to the original table on Period Id.

       

      SELECT

              DEPT,

              ACCOUNT,

              PERIOD_ID,

              PERIOD_ID + 12 AS Next_Year_Period_ID,

              ACT_AMT AS "PY Amount"

       

      FROM ORIGINAL_TABLE

      However, when I join the tables using a Full Outer Join on:

            1. DEPT = DEPT;
            2. ACCOUNT = ACCOUNT;
            3. PERIOD ID = NEXT YEAR PERIOD ID

       

      I only get PY Amounts for Dept-Account-Period ID combinations that have a 2016 budget amount.  I do not get any rows for accounts that are not budgeted in 2016.

       

      Any advice on how to approach this problem would be very much appreciated.  Thank you - Randy