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

    View data at date range intervals from an uncommon baseline

    P Fro

      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:

      Capture.PNG

      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."

       

      Thanks for your help/suggestions!

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

          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
            P Fro

            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
              Joe Mako

              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.