8 Replies Latest reply on Oct 7, 2016 9:38 AM by Hrishikesh Panchal

    Latest Date Filter with value/Data

    Hrishikesh Panchal

      Hello,

       

      I have a question on filtering. For e.g I have below data with different dates for each product and its Price, I want to filter each product with latest date that has Price. If the latest date has Null value/No data then it should look for second latest date with Price Value. How can I do it?

       

      The results should be like column D and E where for Product A although latest date is 4/1/2016, but because it has null value, i want the next latest date which is 1/1/2016 with price 5.

       

      Same way for Product B, i should get latest date as 6/1/2016 and price 20 and for Product C i should get 6/1/2016 and price 3  since 9/1/2016 has null data although it is latest in time.

      Any help will be appreciated.

        • 1. Re: Latest Date Filter with value/Data
          Simon Runc

          hi Hrishikesh,

           

          So this formula should do the trick!

           

          [Show Last Date with Price]

          {FIXED [Product]: MAX(IIF(NOT(ISNULL([Price])),[Date],NULL))} = [Date]

           

          As a FIXED LoD it returns the result to every row, at the level specified (Product in this case). The IIF statement only populates the Date field if the Price is not NULL, and then the FIXED LoD takes the MAX of this. The MAX of "something" and NULL is always the "something". We can then equate, at Row Level, if the Date is the same as the returned value.

           

          Hope that helps, and makes sense.

          1 of 1 people found this helpful
          • 2. Re: Latest Date Filter with value/Data
            Hrishikesh Panchal

            Hi Simon. Thank you for replying and providing me the solution. I think I need to rephrase my question since I do not think i was able to replicate what exactly i was trying to explain for the solution.

             

            Basically my data looks like below and this is just one example of lot number. I have several lot numbers and several records under each lot number. What i am trying to achieve here is that out of all the records from below i just want one record of the latest date which here is 9/28/2016 and value i expect in RESULT column is 54.4. so just one records from all of the below. Same way just one records from other Lot_Numbers.

             

            Not sure if i am able to explain but tried my best.

             

            • 3. Re: Latest Date Filter with value/Data
              Simon Runc

              Yes I think that's what my solution should do...although I left out one very important detail (my bad!)...add that field to the filter shelf and set to true

               

              without filter, data looks like this

               

               

              and with, it just picks the last non-null-price date for each product

               

              • 4. Re: Latest Date Filter with value/Data
                Hrishikesh Panchal

                Hi Simon,

                 

                For some reason it is not working.Also was not able to open your Tableau Report since mine is not an updated version as yours. i will still try to see why it is not working in my file. Thanks you so much for sharing the solution and hopefully it will work.

                • 5. Re: Latest Date Filter with value/Data
                  Simon Runc

                  I've attached the solution here in Tableau 9.0. Hopefully, you can open this one. Let me know if this doesn't solve your issue (and if so why).

                  • 6. Re: Latest Date Filter with value/Data
                    Hrishikesh Panchal

                    Hi Simon.

                     

                    Thank you for sending me the file with Version 9.0, I was able to open it. The formula is working but not giving me the result I want. If you see the second data snapshot, with yellow TAB, i should get date of 9/28/2016 which is the latest date in that data set with Result value of 54.5. But since then the data set got updated and now the latest date is 10/03/2016 but the result value is Null.

                     

                    so as per the formula it should still give me date of 9/28/2016 and value of 54.5, but it is giving me date of 10/03/2016 and result data as NULL value.

                     

                    i hope I am making sense in making you understand.

                     

                    Thanks again

                    • 7. Re: Latest Date Filter with value/Data
                      Simon Runc

                      So you only want one row returned (the latest date, where the result is Not NULL), and not a row per Lot_Number? If so we can just remove the [Product] (or [Lot_Number] in the updated example, so would be

                       

                      [Show Last Date with Price]

                      {MAX(IIF(NOT(ISNULL([Result])),[Day of RModdate],NULL))} = [Day of RModdate]

                       

                      If that doesn't do the trick, can you post the data in your image as an Excel/.csv and I can get it working on your data-set.

                      • 8. Re: Latest Date Filter with value/Data
                        Hrishikesh Panchal

                        Simon,

                         

                        It is working fine now. The reason it did not work before because i was missing the time stamp in data which leaded to confuse the MAX function since there were same date with different time stamp in certain single day.

                         

                        Thanks a lot for helping!! appreciate it.

                         

                        Rishi