3 Replies Latest reply on May 24, 2016 1:05 AM by Rohit Garg

# How to aggregate data into a number using calculated fields and date filtering to compare against another number (calculated similarly just in a different period)

Hello,

Thanks in advance for taking the time to read/answer this post!

Essentially I have 2 numbers, where both numbers are calculated using the same calculated field. The only extra step that changes these numbers would be a date filter to alters the period. The first number looks at the previous 12 months from today; whereas, the second number looks at the 12 months before that. (extra detail: the filter is a calculated field that will filter automatically based on the current day).

Currently, I need to create two sheet in order to show these two numbers because the date filter needs to be pulled into the filter shelf to restrict the period of each sheet. I'd like to create another sheet to be able to get the % change and a sheet to get the difference between these two numbers but I'm not able to do anything that does this. Any thoughts?

Cheers,

Aiden

• ###### 1. Re: How to aggregate data into a number using calculated fields and date filtering to compare against another number (calculated similarly just in a different period)

Hi Aiden,

Using calculated fields, you can perform these actions within the same sheet.

There are many different ways to do this, but here's A solution (there may be better ones out there):

First calc:

IF [Data Field] < TODAY() and [Date Field] > DATEADD('year',-1,TODAY()) THEN [Measure] END

Second:

IF [Date Field] > DATEADD('year',-1,TODAY()) and [Date Field] > DATEADD('year',-2,TODAY()) THEN [Measure] END

Third (% change. this is just simple arithmetic, you can do the math for the difference):

[First Calc] - [Second Calc]

/

[Second Calc]

Hope this helps!

Will

• ###### 2. Re: How to aggregate data into a number using calculated fields and date filtering to compare against another number (calculated similarly just in a different period)

Hi Will,

Thanks for your quick reply. Unfortunately, I've already tried that and it didn't seem to work. With that method, both the calculated fields are converted to attributes (i.e. ATTR(Current Period Calc) and ATTR(Previous Period Calc)). These two numbers are actually the same unless I filter the dates in the filter shelf, but of course the problem with that is I'm not able to filter by two periods on the same sheet.

Also, when I tried to subtract or divide these two calculations via a third calculated field, Tableau does not yield any numbers.

Cheers,

Aiden

• ###### 3. Re: How to aggregate data into a number using calculated fields and date filtering to compare against another number (calculated similarly just in a different period)

Hello Aiden, were you able to resolve this issue? I am trying to achieve kind of similar functionality.