7 Replies Latest reply on Jun 27, 2016 1:24 PM by Saurabh Siroya

    Filter to last date within a date range

    Jim Mackenzie

      Hi All,

       

      I have a requirement to show one chart with a % over time and another chart that just shows the latest % for each person.

       

      I've tried using LAST() = 0 and have created a LOD [Date] == {FIXED: MAX([Date]) } but neither of these filters work properly.

       

      On my dashboard I want to be able to select a smaller date range in my filter and find the last date within that range for each person. Is this possible?

       

      Sample dashboard attached.

       

      Cheers,

       

      Jim.

        • 1. Re: Filter to last date within a date range
          Jim Mackenzie

          Hi Shawn,

           

          The numbers are just test data. If you look in the spreadsheet you'll see that there are two numbers, done and outstanding, per day and the Done % is the % of items completed that day.

           

          For the dashboard there is a line graph showing the done % over time for each of the users (sheet 2). There is also a current percentage (sheet 1), which should show just the latest done % for each person, not an average over the time period.

           

          What I want to be able to do is change the time period using the date slider and have sheet 1 show the last value for each person for the date period. Does that make more sense?

           

          Cheers,

           

          Jim.

          • 2. Re: Filter to last date within a date range
            Carl Slifer

            Howdy Jim,

             

            Your LOD Filter wouldn't work because we've implemented it incorrectly. You've asked Tableau to Fix the date at the max level for the entire database because you did not define what level of detail to fix it at. Try this:

            [Date] = {FIXED [Person]: MAX([Date])}

             

            This is fixing it at the person level of detail and make this a filter, allow only true. This will return the Max Date per person based on everything that comes out after a Context Filter. 

            Now drag your date filter to your filters shelf. I believe you had it set to continuous. Right click this within the filters shelf and set it to 'add to context' This makes it filter the data from your database before the FIXED calculation is ran.

             

            Now when you drag your filter bar around you will change what information could be possibly be included in the LOD so if the LOD is working (it should now) we get the maximum date for each person based on the range you've selected.

             

            Cheers,

            Carl Slifer

            InterWorks

            2 of 2 people found this helpful
            • 3. Re: Filter to last date within a date range
              Jim Mackenzie

              Awesome!! Thanks Carl.

               

              I'd tried tying the LOD to the person before and this was close but it was still running before the date range filter. Adding the date range filter to the context worked a treat and solved the problem.

               

              Thanks again!

              • 4. Re: Filter to last date within a date range
                Carl Slifer

                Hi Jim,

                 

                Not a problem at all Jim. Common mistake. Mastering the order of operations will make life so much easier - Evolution of the Order of Operations Diagram

                 

                Cheers!

                • 6. Re: Filter to last date within a date range
                  Saurabh Siroya

                  Hello,

                   

                  I have a business requirement where I want to use the relative filter but the current date should be a user input and based on that I should be able to select Weekly, Monthly and Quarterly view. Or is there a way to create a date as an input for the user and based on that I will have a list from which I can select weekly, Monthly or a Quarterly view.

                   

                  Please help.

                   

                  Regards:

                  Saurabh Siroya

                  • 7. Re: Filter to last date within a date range
                    Saurabh Siroya

                    Hello,

                     

                    I have a business requirement where I want to use the relative filter but the current date should be a user input and based on that I should be able to select Weekly, Monthly and Quarterly view. Or is there a way to create a date as an input for the user and based on that I will have a list from which I can select weekly, Monthly or a Quarterly view.

                     

                    Please help.

                     

                    Regards:

                    Saurabh Siroya