13 Replies Latest reply on Nov 25, 2013 9:53 AM by Timothy Stark

    Maximum date in all data, not just data in displayed partition / view

    Timothy Stark

      Hi All.   Have a problem I can't figure out how to solve.

       

      I need to display the maximum date that exists in a data set in the title of the worksheet.    I know this will work to display the maximum date in the displayed partition.

       

      total(max([Date]))

       

      But I want the maximum date that includes data filtered out of the view.   I can't use the trick of creating a dashboard and having the tittle be a different worksheet.   The worksheets will be rather long and go across multiple pages so I need the header functionality that worksheets provide.

       

      Any ideas?  Sample workbook is attached. 

        • 1. Re: Maximum date in all data, not just data in displayed partition / view
          Bruce Segal

          Tim:

           

          See the attached workbook & jpgs. There are a couple of ways to do this.

           

          All I did here was change the default format of the calc'd date field you created from "Automatic" to "d/m/yy" I did this in the measure box.

          See the screen shots. Start shows you the formatting in your .tbw file. After shows what I changed the formatting to.

           

          Instead of creating a duplicate date field, you could also do this by using the original date dimension, setting the default format there and putting it on the detail shelf and changing it to a Max measure.

          • 2. Re: Maximum date in all data, not just data in displayed partition / view
            Timothy Stark

            thanks bruce. 

             

            sorry.  maybe i wasn't clear.  i'm not concerned here about the formatting of the date.  i want the title to indicate that the last date in all the data, not just the filtered data, is July 5th.  

            • 3. Re: Maximum date in all data, not just data in displayed partition / view
              Bruce Segal

              Tim:

               

              Let me take another look. My bad. I read it too quickly and didn't realize you want to see 7/5 not 7/3.

              • 4. Re: Maximum date in all data, not just data in displayed partition / view
                Bruce Segal

                Tim:

                 

                Sorry, I don't have a direct answer for you.

                 

                Your data set has sales for apples on two days 7/1 and 7/5. However, the worksheet you show only has data for oranges and oranges only have sales for two of those days; on 7/2/13 and 7/3/13.

                 

                Therefore to my knowledge the max date you can show on that work sheet is 7/3/13 b/c that's the only date in the data that's being shown. I played around with a few custom calcs, but couldn't get them to work. Perhaps others here, can point you to a custom calc that will display the date you want.

                 

                Alternatively, you can create a separate worksheet that shows the max date in the data and combine that into a dash board. For me, I like to make it clear if the data I'm showing indicates that apples and oranges didn't sell on every day of the month. In the analysis I do that can lead to an insight that would be otherwise disguised. But I don't know if that applies to your situation.

                • 5. Re: Maximum date in all data, not just data in displayed partition / view
                  viraj gholap

                  Hi Tim,

                   

                  We faced same issue and checked with Tableau consultant. He recommended adding additional column in the data source named MAX_DATE that will have the max date for every record in the data source. We implemented this approach on DB side and all works fine.

                   

                  This does not look nice solution but it works perfect!

                   

                  Regards,

                  Viraj

                  • 6. Re: Maximum date in all data, not just data in displayed partition / view
                    Bruce Segal

                    Viraj:

                     

                    Interesting approach. I want to make sure I understand it so I can consider it as a solution.

                     

                    Are you doing this in a dbase or .xls or some other data source? Are you doing it manually or automatically?

                     

                    Do you mean that every row in the column Max-Date has the same date in it? And that you overwrite and replace the date in that column in every row, every time the max date in the data set is higher than the last time?

                    • 7. Re: Re: Maximum date in all data, not just data in displayed partition / view
                      Joshua Milligan

                      Bruce,

                       

                      Here are a couple of other options that will allow you to get to the max date in the data without constantly updating the source (though that might be a preferred approach in some circumstances).

                       

                      1. Use a table calc filter.  Table calculations are preformed post-query, so the data exists in the data engine to get the max, but the table calc will effectively hide other values.
                      2. Create a copy of the data source and use data blending to get the maximum date in the data.  I created a calculated dummy field to link the filtered data in the primary source to all the data in the secondary source.

                       

                      I've attached your workbook with a couple of tabs to demonstrate both approaches.  I'd be happy to answer any questions you might have!

                       

                      Regards,

                      Joshua

                       

                      1.

                      0.png

                       

                      2.

                      0.png

                      1 of 1 people found this helpful
                      • 8. Re: Maximum date in all data, not just data in displayed partition / view
                        viraj gholap

                        Hi Tim,

                         

                        We are doing it in database and automatically.

                         

                        Yes for every row, Max-Date column has same value. We overwrite it each time data is refreshed.

                         

                        Regards,

                        Viraj

                        • 9. Re: Re: Maximum date in all data, not just data in displayed partition / view
                          Bruce Segal

                          viraj gholap : Thanks for providing the detail. It's helpful to know.

                           

                          Joshua Milligan: Thanks for posting the work book. This is Tim's question so ultimately he has to decide if any of the options you present will work for him. your table calc solution is exactly what I was envisioning in my comment that perhaps others can propose one as a solution. I'm going to use that model for some other issues I encounter.

                           

                          I get that you've effectively recreated the Dimension product name as a Measure that displays the text of the Product dimension. And hat b/c it's a Table Calc, when we use it as a filter on product types it sees the data (and dates) in the data set and not just the dates for what's displayed in the view?

                           

                          Yet, I have questions about how it works that will help me understand how to apply this type of solution in other situations.

                           

                          Calc name (measure): Product (Table Calc Filter)

                          Formula: LOOKUP(ATTR([Product]), 0)

                           

                          When I look at the calc, I see:

                          1. It works on a Dimension in the data named Product;
                          2. The table calc is a measure, not a Dimension;
                          3. It displays text - the names of the Products - just like original dimension, not numbers.
                          4. You use the LookUp function set w/ a 0 offset, and the ATTR function.

                           

                          I'm going to play around with the formula to see if I can figure out how it works. In the meantime, am I correct in understanding the following?

                          1. we need to use the ATTR function as a workaround to turn a dimension into a calculated measure. And the function treats the text behind the scenes in the Product Dimension as a Number that will display as text,
                          2. we use the LookUp function (with the offset = 0 as a kind of placeholder) so the formula displays the text and does so w/o looking back or forward.
                          • 10. Re: Re: Maximum date in all data, not just data in displayed partition / view
                            Joshua Milligan

                            Bruce,

                             

                            What you've stated is essentially correct.  When dimensions and measures are used in a view (on Rows, Columns, Color, Text, etc...) they generate a query to the underlying data source.  When the results are returned to Tableau, they are returned as a data table containing just enough rows for Tableau to render the viz (so, for example, a bar chart with 5 bars may result in a query that aggregates millions of rows into a table with 5 rows of aggregate data).

                             

                            Table calculations are performed on the resulting data table.  The LOOKUP function allows you to lookup values in the data table at various offsets (the 0 offset does indeed mean that it is not looking backward or forward).  Table calculations always operate on aggregates.  That's why ATTR is used.  It treats the dimension as an aggregate.

                             

                            Using that calculation as a filter still allows unfiltered data from the source to be retrieved into the data engine and thus other calculations can reference values that are not shown.

                             

                            Regards,

                            Joshua

                            • 11. Re: Re: Maximum date in all data, not just data in displayed partition / view
                              Timothy Stark

                              Thanks everybody, but particularly to Josh & Viraj.

                               

                              In this case I went with adding a max_date column to the datasource, which is a view built in Oracle.  The query from Tableau is just a 'select * from ...' on a several million row database, so this was the most straightforward solution.

                               

                              Josh:  I liked both of yours, particularly the data blending option.   I will almost certainly use one of your proposed solutions in the future.    You even lead me another way of linking....

                               

                              Create two data connections.   Instead of Connection 2 being a duplicate of Connection 1 it is just 'select max(date) from...' and then add the dummy link variable to each connection.  Blend and there you have it!

                              • 12. Re: Re: Maximum date in all data, not just data in displayed partition / view
                                Bruce Segal

                                Tim:

                                 

                                I can't take credit for those two solutions you mention. It goes to Joshua Milligan His solutions helped me too.

                                 

                                Thanks Josh