3 Replies Latest reply on Feb 7, 2013 2:55 PM by Joe Mako

View data at date range intervals from an uncommon baseline

I have a flat file of transactions for several different "events." For simplicity sake, I'll use the "Toy Story" movie "baseline calc" from the Tableau website as an example. However, my data is not structured the same way that theirs is...mine is transactional level.

It is a flat file of income by date broken out by movie release date like this:

What I want to do is create a parameter where I can change the number of days "out" I am for analysis to see that at x days, I here's what each movie made. I have created a calculated field to find out how many days out I am (max date minus release date) but I'm not sure where to go from there. How do I link that into a parameter and filter out any data for dates past x? (which would rely on the release date for each movie)

This is probably so easy, but I'm having a hard time wrapping my mind around it because of the different "release days."

• 1. Re: View data at date range intervals from an uncommon baseline

Here is one option (starting with a fresh connection):

1. create a parameter called "Days Out", that is an integer.

2. create calc field "Amount in Range":

IF [date]-[release date]<=[Days Out] THEN [amount] END

Sum this field, and you will have the sum amount within the "days out" range.

If you want to see this as a running total, then:

1. create a calc field named "Day":

INT([date]-[release date])

2. another for use as a filter, keeping only when True:

[Day]<=[Days Out]

3. place Day as a continuous discrete on the Columns shelf

4. place "amount" on the Rows shelf, and enable the Running Total Quick Table Calculation

this is also in the attached.

• 2. Re: View data at date range intervals from an uncommon baseline

Thanks Joe, that works perfectly and even answers my logical follow-up question!

Although I'm not really understanding the rationality behind: IF [date]-[release date]<=[Days Out] THEN [amount] END

How does it know for example, that IF 10<=15  THEN only display the total amount for the first 15 days?

(You don't have to answer that if you don't want to...just trying to wrap my brain around why it works)

• 3. Re: View data at date range intervals from an uncommon baseline

The comparison test of [date]-[release date]<=[Days Out] happens at the row level in the data source, in your example of 10<=15, that would result in True, so the IF statement would return the value for [amount] for that record in the data source.

Then the SUM() aggregation in the pill aggregates all those records that passed the test, resulting in the sum of amount for those dates in the desired range.