6 Replies Latest reply on Feb 28, 2019 11:24 AM by Jim Dehner

    Get last non-null value

    Justin Joseph

      I am working  on creating a bullet chart with multiple API(actual performance index) for API Survey, API Drilling, API Layout, API Recording and API Pickup. The API is a performance index measurement in percentages. The problem I am having is that the different phases start and finish at different dates and I am trying to get the last non-null value, but have been unsuccessful as I am newer to Tableau. Please let me know if anyone need details. There is a lot of extra fields in the workbook, but the only fields you should look for is the API in them

        • 1. Re: Get last non-null value
          Jim Dehner

          Hi Justin

          wow your workbook is complex

          and you have a different date field for each of your measures - I'm not certain I understand the goal but your individual last dates can be found with 4 formulas like this

           

           

          Now comes the problem - there are 4 different date fields that really don't know one another - it you require creating a single continuous date frame and associating each or your dates with that continuous frame - see scaffolding

          TDT: Data Scaffolding with Joe Mako

          don't know if you are looking at these 4 areas independently or combined - if they are individual you can use the above formulas - if you want to look at a combined result the solution is much more complex

           

          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 of 2 people found this helpful
          • 2. Re: Get last non-null value
            Simon Ford

            Hi Jim,

             

            Is there a way of using this to calculation to filter the view?

             

            Many thanks

            Simon

            • 3. Re: Get last non-null value
              Jim Dehner

              Hi Simon

              that depends on what you are trying to do

              if all you want to do is know the sales on the last day by sub category

              You can do something like this

               

              but that is the only data you have - 17 marks - if you wanted to do any sort of YOY or running totals you would have to take a different route

               

               

              Jim

              1 of 1 people found this helpful
              • 4. Re: Get last non-null value
                Simon Ford

                Hi Jim,

                 

                I can't see the second picture unfortunately,   but I am trying to show a single day (or date range) showing the most recent entry to a spreadsheet based on data being in a particular column:

                 

                In a nutshell, I am trying to get it to show me the 22st of December based on the fact that was the last date any data was entered, but the dates themselves run to the end of the year.  And have this as a view that updates daily, based on the refreshing of the spreadsheet?

                 

                Any ideas would be much appreciated!  Your formula above works in pulling the date back, now I just want that date in the view.

                 

                Thanks

                Simon

                • 5. Re: Get last non-null value
                  Jim Dehner

                  you can do it this way

                   

                   

                   

                   

                  it will return this

                   

                  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.

                  • 6. Re: Get last non-null value
                    Jim Dehner

                    I'm sure you'll figure it out

                    Thanks

                    Jim