13 Replies Latest reply on Jul 7, 2019 2:27 AM by Simon Runc

    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.

          3 of 3 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.

                  1 of 1 people found this helpful
                  • 6. Re: Get last non-null value
                    Jim Dehner

                    I'm sure you'll figure it out

                    Thanks

                    Jim

                    • 7. Re: Get last non-null value
                      Preeti TPCI

                      Hi

                      Simon Runc

                       

                      I want the viz to return the last non null value because for some labels it may be updated for the current month but not for others. Hence when one users select a month I want viz to return the last non null value i.e. updated for the updated ones and old value for the non updated ones.

                      I created a calculated field pivot non null value

                      code below

                      If ISNULL(sum([Pivot Field Values]))

                      then LOOKUP(sum([Pivot Field Values]),-1)

                      ELSEIF  ISNULL (LOOKUP(sum([Pivot Field Values]),-1))

                      then LOOKUP(sum([Pivot Field Values]),-2)

                      ELSE LOOKUP(sum([Pivot Field Values]),-3)

                      end

                      There is little likelihood of lag of more than 3 months, hence this code.

                       

                      However I am unable to get the desired result. Kindly help!

                       

                      Also  I want to show previous year data for the same month along with the select value. Eg. if one selects may 2018, the viz should show both may 2018 and may 2017. How can I do that?

                       

                      Third query is, I calculated YOY field  and calculated a sign  field for up/down KPI indicators, but I want it to calculate it only for the current year month i.e. may 2018 and not the previous year month i.e. may 2017.

                      Codes I used

                       

                      YOY field (it calculates table (across)

                      (SUM([Pivot Field Values])-LOOKUP(SUM([Pivot Field Values]), -1)) / LOOKUP(SUM([Pivot Field Values]), -1)

                       

                       

                      sign field

                      IF  [Signvalue] > 0  then "UP"

                      ELSEIF [Signvalue] < 0 then "DOWN"

                      ELSE "No Change"

                      END

                       

                      But it is not working fine, I am unable to understand how it calculated what it calculates

                      Data and dashboard-

                      Tableau Public

                       

                      Please help

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

                        So quite a bit going on here. The way you have tried to get the last non-null value, using LOOKUP, is via a Table Calculation. These depend on the level of detail you want the LOOKUP to run over in the Viz. So in your case for this to work you'd need Month in the Viz, which you don't

                         

                        An easier way is to use an LoD, like this

                         

                        if [Year1] =

                        {FIXED [Pivot Field Names - Split 1], [Pivot Field Names - Split 2]:

                        MAX(iif(NOT(ISNULL([Pivot Field Values])),[Year1],null))} then [Pivot Field Values]

                        end

                         

                        So the FIXED LoD part looks for the last non-null value date (for each Pivot Measure Split) and then just returns that value. I'm not sure how you'd want this to work if someone changed the Month, as this would no longer work. I also, personally, don't think it's a good idea if someone selects May 19, but the data they see is actually a combination of some May, some April and some March. If you want it to be dynamic on a month selection, you'll likely need a Table Calculation ...but things will get more complicated.

                         

                        On the second question. Again it's not really possible (without a lot of complicated LoDs) to have when you filter the data to a single month/year, for the data for the previous year to be access-able. An easier workaround is for someone to select the Month (this way they will filter to May, and you can still have May TY and LY unfiltered so you can do the comparison. Another way is via an action, where you can choose to only send the Month Part.

                         

                        On the final question. I can't see where you've used this, but the formula is correct. But again it's a table calculation and so the result depends on how you set it up and the level of detail in your viz. If you set up the table calculation, filter and Viz Level of detail correctly, it won't show the YoY for the LY value.

                         

                        I'd suggest you start a new thread for each question (happy for you to ping me, but I'm out most of next week), with a single worksheet explaining what you want to do, and where you are stuck. It's quite hard to look through a multi-sheet workbook, with many datasources and understand what you are trying to achieve and where. In Tableau any aggregate calculations (Aggregates and Table Calculations) depend on the level of detail in the viz on how they work, so your final use/viz will affect the solution. If you think of something like SUM([Sales]); if you have no pills in your Viz it returns one value, but if you have Product, say, in the Viz it returns different values ...this is simple example, but hopefully the general point makes sense.

                         

                        Hope that helps

                        2 of 2 people found this helpful
                        • 9. Re: Get last non-null value
                          Preeti TPCI

                          It might take a while for me to absorb and apply all that is suggested by you. Thank you so much. Yes I agree it is confusing for the end user. I shall try to do something about it. Thanks

                          • 10. Re: Get last non-null value
                            Simon Runc

                            No problem. I’d watch the Tableau Learning Video on Table Calculations, as

                            this gives a good introduction to the concept. Also the one on LoD

                            expressions.

                             

                            On Sat, 6 Jul 2019 at 08:23, Preeti TPCI <tableaucommunity@tableau.com>

                            1 of 1 people found this helpful
                            • 11. Re: Get last non-null value
                              Preeti TPCI

                              How can I fetch the month year of the non null value returned by the

                               

                              if [Year1] =

                              {FIXED [Pivot Field Names - Split 1], [Pivot Field Names - Split 2]:

                              MAX(iif(NOT(ISNULL([Pivot Field Values])),[Year1],null))} then [Pivot Field Values]

                              end

                              1 of 1 people found this helpful
                              • 12. Re: Get last non-null value
                                Preeti TPCI

                                Done thanks!

                                • 13. Re: Get last non-null value
                                  Simon Runc

                                  Cool. Yes it's just this part of the formula which gets this

                                   

                                  {FIXED [Pivot Field Names - Split 1], [Pivot Field Names - Split 2]:

                                  MAX(iif(NOT(ISNULL([Pivot Field Values])),[Year1],null))}

                                  1 of 1 people found this helpful