7 Replies Latest reply on Feb 2, 2019 6:28 PM by Reg Chand

    Tableau Aggregation when using IF's with Left and grandtotal

    Reg Chand

      Hi All,

       

      I've been struggling to get the aggregation to work correctly. Basically I have a data set which is driven off by the financial period e.g. 201901 is April 2018. I have data from beginning of time and need to aggregate or have a running total.

       

      lets say I have amount for each of the period (Amount is un-grouped and is by chart of account). First thing I did is in SQL wrote a query to get current year, then last year.

       

      In Tableau first formular is;

      IF LEFT(STR([Period]),4) <> STR([lastFinYear])

      AND LEFT(STR([Period]),4) <> STR([ThisFinYear]) THEN [Amount] END (this totals everything that does not match year 2018 and 2019 (current and last financial year)

       

      Next Formular

       

      IF LEFT(STR([Period]),4) = STR([lastFinYear])

      AND LEFT(STR([Period]),4) <> STR([ThisFinYear]) THEN [Amount] END (This totals last year data only)

       

      Next Formuar

      IF LEFT(STR([Period]),4) = STR([lastFinYear]) and

        RIGHT(STR([Period]),2) = '01' then [Amount] END (this looks at April 2017 Or (last year april) month only where period  = 201801 so if it matches last fy year (2018) and period 01 then April

       

      Idea is I total all three above and use parameter to select year/month This works on grandtotal level however not all values are showing on table.

       

       

      See attached packaged workbook.