1 Reply Latest reply on Jun 28, 2013 12:28 PM by Mark Holtz

    Maximum value from table regardless of filters applied?

    Chad Kovac

      Is there any way to get the MAX of a field from outside the data being displayed on the view?

      If I have Dates and I want to know the MAX Date in the entire table without any regard to the filters on the actual sheet, how can I do that? 

      For example, I have a filter that shows me January 1-3, 2010.





      but the largest in the table is actually 6/1/2011.


      I'd like to see:

      1/1/2010  6/1/2011

      1/2/2010  6/1/2011

      1/3/2010  6/1/2011


      but all I can get it to show me is:

      1/1/2010  1/1/2010

      1/2/2010  1/2/2010

      1/3/2010  1/3/2010

      or using window_max:

      1/1/2010  1/3/2010

      1/2/2010  1/3/2010

      1/3/2010  1/3/2010

        • 1. Re: Maximum value from table regardless of filters applied?
          Mark Holtz



          If you just have a single data source and you filter to only keep certain records, the values on those records are going to be all you have available to you in your viz/dashboard.


          You can use a filter and still do what you are after in 2 ways:

          1) Pull a duplicate data source that will not be filtered and bring in your maximum calculation date.

          2) Create custom SQL so that the MAX date you are referring to is present on every single record.


          Yet another option would be to create conditional measure fields rather than using filters. In essence, you could set a parameter up as a date, and each measure would be a calculated field something like:

          IF [Date] <= [ParameterDate] THEN [Measure] ELSE NULL END

          So when you set the parameter to 1/3/2010, each of your 3 example values would show, and the rest would contribute nothing.

          If you actually display the dates, you will probably want to create an additional calculated field to use as a filter, but to keep the last record in the partition.

          If you want to try to go about it this way, I'd suggest you post a packaged workbook so forum contributors can assist you.