2 Replies Latest reply on Jan 17, 2013 11:49 AM by John Garrison

    Dynamic filter based on Date and TODAY

    John Garrison

      I work in a college athletic department and use Tableau to track ticket sales.  I have data for tickets and revenue coming out of our ticketing system but have a problem.  Tickets are "sold" into the ticketing system for all games for one segment of buyers (students).  The system treats it like a season ticket, but it creates the issue of inflated sales and revenue for future games since they are not actual sales until the game happens (sometimes more, sometimes less then the initial number).  This occurs to print the tickets in advance.

       

      I created a calculated field based on the date being < TODAY which returns either "Completed" else "To Come".  I can filter with this to have all sales for completed games or game to come, but what I haven't been able to figure out just the data for those future games.  In other words, I want to include the data in the games that have occurred (Date<TODAY), but exclude the data from those games in the future. I'm not sure I've explained this very clearly, but any help would be greatly appreciated.  thanks, John

        • 1. Re: Dynamic filter based on Date and TODAY
          Andy Piper

          John,

           

          Without a sample of what you're working with, I can only speculate that you might want to consider a T/F calculation based on the equation you specified above ([Date of Game]< Today()). Doing this will place a T/F symbol beside the calculated field name. Place this field on the filters shelf and select TRUE.

           

          This should filter all data to show only those records where the date of the game is before today.

           

          If this doesn't work for you, please consider taking a subset of your data and creating a packaged workbook with it - of course, protect the innocent if names and contact info is present.

           

          Hope this helps,

           

          Andy

          • 2. Re: Dynamic filter based on Date and TODAY
            John Garrison

            I figured out a formula for a filter that works.  Here it is in case it is helpful for anyone else...

             

            IF ([Event Date]> Today()-1) and [Price Type Class] == "STDNT (STUDENT)"

            THEN 'Remove'

            ELSE 'Leave'

            END

             

            Then I use this as a filter and exclude. This includes the student tickets after the game is played, but excludes them prior to the event.  thanks, John