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

# Tableau Aggregation when using IF's with Left and grandtotal

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.

• ###### 1. Re: Tableau Aggregation when using IF's with Left and grandtotal

Good morning

see below you need to rest the Grand Total to Sum

open the green pill (right click) and follow the path

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Tableau Aggregation when using IF's with Left and grandtotal

Hi,

That only fixes the grand-total part which was actually correct. My major problem is if you drag measure LYACT into view then drag measure LYACT00 into view then drag measure TYAPRIL into view all those individual pill show data. however when I add LYACT+LYACT00+TYApril data for account code 116 disappears.

This is what It needs to show when you drag year to date pill into view.

• ###### 3. Re: Tableau Aggregation when using IF's with Left and grandtotal

I certainly didn't get that from the post

the issue is you have nulls in your data set - when Tableau is asked to sum data with nulls it will return a null

I was going to search for the but decided against that when I opened you YTD calculation - each of your individual aggregation should be wrapped in zn()  - e.g.

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

RIGHT(STR([Period]),2) = '09' then zn([Amount] )END

I wrapped the entire ytd calc in a zn just demonstrate that there are nulls - it will  a null if a if there are nulls in the calculation (i.e. wont return the correct answer - it individual aggregations need to fixed)

when I did that it returned a 0 as shown below

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 4. Re: Tableau Aggregation when using IF's with Left and grandtotal

sorry realized where the nulls are coming from

each of these statements (like the one shown below) will return an Null when the conditions are not met

so what to do

wrap each with a zn()

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 5. Re: Tableau Aggregation when using IF's with Left and grandtotal

Thanks Jim, That worked, i've been trying to get this work for the last two days, couldn't understand why when each pill pulled individually the amount works but, does not work in aggregation. Null's did not cross my mind.

So basically if there are nulls in calculation the line level and Grandtotal won't work? I will always remember