4 Replies Latest reply on Aug 24, 2018 12:34 PM by ashish.tamboli

    LOD to capture max date

    ashish.tamboli

      Hi All,

       

      My tableau dashboard tables left joined to a single table called as Calendar. This Calendar table has only one column as Date.

      All other tables are left joined using date column to the calendar table.

       

      In One of the sheets I have used the date field from calendar table. i.e. calendar.date_field as special filter to show all values. I have done show filter to show the slide bar for users to change the end date (last date or max date) whatever we can call.

      the data in the sheet changes dynamically. So far so good.

       

      when user selects any date as end date or max date from the filter, the data in the sheet should be previous 30 days based on user selection. so i wanted the max date (user selected end date or max date) to do minus 30. so i used the WINDOW_MAX function and it gives me the max date and i am able to do minus 30 days. and show data accordingly.

       

      However i realised that due to left join of all the tables to the calendar table, there are obviously NULL values in the data because there cannot be data/ values for all the dates present in the calendar table. So i tried to filter the null values. When i filter the null values my WINDOW_MAX date is changing and calculation of 30 days is getting wrong.

       

      I want to keep the window max date or the user selected end date / max date as it is even after removing the Nulls. I tried few LOD expressions but did not get anything yet.

       

      Any help is really appreciated.

        • 1. Re: LOD to capture max date
          Jim Dehner

          Hi I looked at your book but could not look directly at the data and the connection

          I did something similar for a client yesterday and create the join shown below

          the "Sheet 1" is just a date ladder that encompasses the entire date range plus

          the Start and End dates are from his data - he was looking at when events were active between a start and end date

           

           

          the join resulted in a record (real record) for each date/project combination - they could then place Date on the viz and on a context filter and still use a slider on to limit what was shown in the viz -

           

          I can't tell looking at your data - which date(s) to use - but it might give you a thought

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: LOD to capture max date
            ashish.tamboli

            Below id how my data source looks like.

             

            Sheet 1 is from excel having all the dates of 5 years lets say.

            And the remaining tables are left joined on date column. there are almost 8 tables which are left joined to the Sheet1 based on the date column.

             

            Regards,

            • 3. Re: LOD to capture max date
              Zhouyi Zhang

              Hi, Ashish

               

              Is it something like this? if yes, please find attached workbook.

               

               

              Hope this helps

               

              ZZ

              • 4. Re: LOD to capture max date
                ashish.tamboli

                Thank you. It worked as expected.