1 2 Previous Next 19 Replies Latest reply on Jul 12, 2018 1:38 PM by Jana STUPAVSKY

    Return latest value available in column

    Jana STUPAVSKY

      I track various economic indicators on monthly and quarterly basis and they are added to my spreadsheet at different times of the month. I have 10 tiles in my dahsbaord showing 10 different indicators. 8 of them are tracked monthly and two quarterly.  I would like that my dashboard shows at any given time:

      1) the latest value available for each indicator with corresponding month/year (e.g. Value for May 2018 or Q1 2018)

      2) value right before the latest value with corresponding month/year (Value for April 2018 or Q4 2017)

       

      I have bunch of IF formulas doing it right now, but they are not "dynamic enough." I suspect LOD function would help, but I don't have much experience with them. Any help will be much appreciated.

        • 1. Re: Return latest value available in column
          Steve Martin

          Hi Jana,

           

          This is easy enough with table calculations, the basis of which uses a Last() for your final value and Last()-1 for the penultimate value eg:

          Window_Sum(Sum([Mortgage Interest rates  (15 year)]),Last(),Last())
          

           

          See my attached for your "15yr Motg Tile - Rebuild" and the sheet "Dashboard Dupe": Initially, it looks the same but, I have added a parameter in so you are able to adjust the period back by 10 months from the opening number allowing you to compare - I have built a "raw" sheet for this comparison.

          This is only on the final value and is purely for you to test for yourself.

          If you want to go ahead with my solution, simply Edit the [Final Value] calculation, removing the active line (that includes the parameter) and uncomment the calculation above it - unless you like the idea of a parameter in which case, keep it as is (you will need to make teh same adjustment to the [Penultimate Value] calculation.

           

          Steve

          • 2. Re: Return latest value available in column
            Jana STUPAVSKY

            Steve - thanks for your reply. There was no attached workbook. Would you please re-send? I am trying to do it following the formula you suggested, but I am getting the SUM of the entire column. 

            • 3. Re: Return latest value available in column
              Steve Martin

              Hi Jana,

               

              There is a workbook attached to the previous post

               

              Steve

              • 4. Re: Return latest value available in column
                Jana STUPAVSKY

                OK -  I see it now, but I don't see any of your stuff in it. It looks like my original.

                • 5. Re: Return latest value available in column
                  Jana STUPAVSKY

                  Actually - I see the problem now. It appears you may have a newer version of Tableau and it won't open it for me. Could you save in an older version?

                  • 6. Re: Return latest value available in column
                    Steve Martin

                    No, that is my edited one, look at the dashboard name - "Dashboard Dupe" and at the top right, there is the parameter I mentioned in the previous message.

                     

                    Attached again, just in case.

                     

                    Steve

                    • 7. Re: Return latest value available in column
                      Jana STUPAVSKY

                      Here is the message I am getting:

                      Errors occurred while trying to load the workbook  The load was not able to complete successfully.

                      This file was created by a newer version of Tableau. Please upgrade Tableau to the version (or greater) that the file was saved in.

                      • 9. Re: Return latest value available in column
                        Jana STUPAVSKY

                        Thanks - I think I like the "raw" approach better, simply because I am having charts with historic values at the bottom of the dashboard.

                         

                        So to use your "raw" approach, how exactly would I build my tile? I am trying to do it with CPI - see attached. It seems to me that I would have to manually filter for the month/year to show, am I right? If so, that is not what I am looking for. I want the month/year change dynamically with corresponding Final (I am calling it Latest) and Penultimate (Previous) value. Is that doable?

                        • 10. Re: Return latest value available in column
                          Steve Martin

                          No Jana, my approach of using the Last as a filter will negate the use of a date filter or anchor.

                           

                          A couple of row numbering functions that will really help you here are: Index(), Last(), and First(); the output of which looks something like this:

                           

                          Index()   Last()   First()   Product   Location

                              1            9         -1       Ball         NA

                              2            8         -2       Bat          NA

                              3            7         -3       Ball         EMEA

                              4            6         -4       Bat          EMEA

                              5            5         -5       Car          LATAM

                              6            4         -6       Horn        APAC

                              7            3         -7       Bell          NA

                              8            2         -8       Hat          APAC

                              9            1         -9       Cake       EMEA

                             10           0         -10     Coat        LATAM

                           

                           

                          So as you can see:

                          • Index plots from 1 to n
                          • Last from n to 0 and
                          • First() from -1 to n (functionally the same as Index())

                           

                          So with this in mind, if you look how I have built my comparison sheet, I have the Last() function as a filter, set to 0 so this will hide all other values in the table, and, the current month and penultimate month values for the tile itself are controlled using a window_sum with the Last() function for the current month and Last()+1 for the penultimate month therefore, my tile, whilst it looks similar to yours, is actually different as this will always be the final value.

                           

                          So, to build it, forgetting the "Raw" sheet as that is there as a check-back to builder (don't forget with Tableau, you build a table first, check your output and then convert to a chart, never begin with the chart):

                          For the month & year calcs, taking my two calculations "Final" and "Penultimate", duplicate them for each of your tiles and replace the value they are using "15yr mortg" (something like this - I need to check) and replace each with the new value.

                           

                          Then, duplicate my tile for each of the new calculations and replace the measures pill in each.

                           

                          Once you get this far, the rest becomes easier.

                           

                          Steve

                          • 11. Re: Return latest value available in column
                            Jana STUPAVSKY

                            Steve - thanks for clarifying. I hate wasting your time, but showing these three functions helped.

                             

                            Now - I am following what you are telling me. I developed a couple of new tiles following your copy-paste suggestion - one was for the same indicator you did (15 Yr mortgage rate) and another one was for CPI - Consumer price index. While the first tile copied from you looks just fine because my latest value in the data source for 15Yr mortgage rate is from May 2018 and the Penultimate from April 2018, the other tile, the CPI does not. My Data source has the latest CPI value from April 2018 and the penultimate value from March 2018, but the tile is showing blank for May and then April as Penultimate. Also - the months in the text field are my old calculations and they don't correspond with CPI values available in the data source. Is your solution also proposing how to display corresponding month/year? Am I missing it somewhere?

                            And how could I utilize the same approach for one of the two indicators (Real GDP) that are only updated quarterly, rather than monthly? 

                             

                            Also - could you elaborate on this a bit? You are explaining below you have the LAST filter set to 0, but when I pull it up, it shows range 1 to 1. Am I missing something?  

                             

                            And lastly - When I copy and paste your worksheets into my workbook, it creates a new version of the data source (see attached screenshot). Do you know of a way to get rid of this? I am trying to remove connection and perhaps reconnect with the original, but I am not given an option to remove the connection.

                             

                            • 12. Re: Return latest value available in column
                              Steve Martin

                              Ok,

                               

                              I'm guessing you have little experience with window calculations - for the purpose of the thread, I shall assume not.

                              Window Functions:

                              Unlike all other calculations (calculated field) that are executed by the underlying data engine for which Tableau merely plots the values as a chart, window functions (and other table calculations such as lookup) operate on data in the visual space, and are therefore processed by Tableau itself.

                               

                              So when you run a calculation eg: Sum(Sales) or If Region = 'APAC' Then Sum(Sales) Else Sum(Sales)/2 End - these calculations are executed by the underlying source (eg SQL Server or Jet for Excel / Access / flat-file etc)

                              But, a table calculation is executed by Tableau eg Window_Sum(Sum(Sales)) as such, the value to be modified needs to first be aggregated hence the double sum in the expression "_Sum(Sum("

                               

                              And, as the table calculations are executed within Tableau, they are only executed on the materialised data which is to say that unlike a database table, where you would need to specify a range of rows to be included or excluded from the set based either a filter, a join (to filter) or a top, table calculations use a defined window-space which is what you see once you have plotted some rows and columns.

                               

                              So taking a look at this image:

                               

                              If we use an index() with no scope (table down / table across) then the output will match the blue section, but, changing the scope to "product" in Tableau will set the scope to the corresponding grain in this case, region so the numbering will be restarted as each new region is added.

                               

                              In most cases (except when a table calculation is being used as a filter), adding a filter to a worksheet in Tableau will filter the underlying data which is not what is wanted here so, using table calcs, we can cherry-pick the numbers we want without harming the final set, simply by defining a window.

                               

                              So if for example I created this calc: Window_Sum(Sum(value)), with no scope defined, this would sum the entirety of value (210) (blue) and place that number on every row; whereas altering the calculation to Window_Sum(Sum(value),First(), First()+1):

                              • No scope set, would sum(17,13) (the first two entries of the whole set and place the result on every row (dark yellow)
                              • Setting the scope to product (region) would sum (17,13) = 30 the first two entries of the North group and place against each row of the North group (dark yellow), and then the first two entries of the South group (22,10) = 32 and place this against each row of the south group (dark green)

                              I hope this brief overview of table calcs helps

                               

                              Your Answers

                              So now we have table calcs dealt with, going back to the questions:

                              1. By using a non-scoped window sum to calculate the final and penultimate values (you could also use Lookup() here to the same effect), I am calculating (or rather identifying) the final and penultimate values of the set and placing these as a new entry on every row of the set irrespective of the original value; this was the purpose of the "Raw" sheet, so you could see what was going on. So, for your cpi data, as the date within your source goes as far as May 2018 but the cpi data ends in April 2018, then you only need to alter the calculations of final and penultimate:

                                Current Final: Window_Sum(Sum([Consumer Price Index]),First(),First())
                                New Final: Window_Sum(Sum([Consumer Price Index]),First()+1,First()+1)
                                Current Penultimate: Window_Sum(Sum([Consumer Price Index]),First()+1,First()+1)
                                New Penultimate: Window_Sum(Sum([Consumer Price Index]),First()+2,First()+2)

                                So by adding an additional row, we have effectively moved the row lookup from the last row in the set to the penultimate row in the set (for final) and again for the penultimate
                              2. The second point of Last = 0, yes, you are right, I am using Last = 1, but now, hopefully you will understand that purpose of the Last() filter is only to select a row from the output, it matters not which row this is as by using a window calc, the same value is repeated across all of the rows irrespective of the physical data of that row.
                                Using a table calc as a filter is not sent out to source because this is an in-Tableau calc, what Tableau will do is calculate the value for all of the rows and then, hide-away the rows that do not correspond to the last filter


                              3. When copying a sheet, even if it is using the exact same data as the rest of the workbook, Tableau will copy the data and source that the copied sheet is using. You do not need to copy the sheet into a new workbook, just rebuild it in the new book. But if you do need to import as you have, a copy will be made, but, being only a semantic layer, this can easily be removed:
                                1. Right-mouse select the unwanted data-source and select "Replace Data Source..." from the context menu
                                2. As there is only the two sources, Tableau will pre-populate, just ensure that the copy is set as the Current and the actual one is set to Replacement

                               

                              I hope this helps you to understand the process behind. If you are still struggling, I can give you a hand to build this.

                               

                              Steve

                              • 13. Re: Return latest value available in column
                                Jana STUPAVSKY

                                Hi Steve - I really appreciate you taking such elaborate approach in explaining the Window calculations. I really had no experience with them and I ended up building a raw sheet myself just to play around with them. So again, thanks a lot.

                                Two things:

                                1) In answering my 1. question, in the calculations you made (Current Final, new final, etc) ... you actually meant to have LAST() in there instead of FIRST(), didn't you? I get null using your formulas, but I get therm right if I swap FIRST for LAST.

                                 

                                2) I get the FIRST()+1 and LAST()-1, ...but does it mean that I will have to manually change the formulas (in other words, adjusting 0, -1 ,etc) as I add new values to the bottom of my columns ( CPI, 15 yr mortgage, etc)?

                                For instance - I have the CPI and 15 yr mortgage formulas set up slightly differently now because for CPI I have my last value from April 2018 and for 15 yr mortgage from May 2018. It's working for me now. But tomorrow, or in a few days, I will add May 2018 value to CPI column. And then, the current CPI formula won't work for me. And the same will, I guess happen to my quarterly data, as I will have to change the formula manually to -1,, -2 or -3  as new months are added to the rows..... And that would be applicable to several other columns, since I track multiple indicators. 

                                Basically, I would like that my calculations pull the very latest value from each column at any given time, without being adjusted manually. Is there a way to accomplish that? Or am I missing something here in your explanation?   

                                • 14. Re: Return latest value available in column
                                  Steve Martin

                                  Hi Jana,

                                   

                                  Apologies for the lateness of my response.

                                   

                                  1. Yes, you are correct, I did mean Last(0 instead of First() - First will be for dealing with entries at the beginning (top) of your data file.

                                   

                                  2. As for altering the window values, I suggested this method so you will not have to keep adjusting the date - in essence, the window will always remain the same as the data points will always be the penultimate and last points.

                                  But, as you have explained that your data is loaded at differing times, it will probably be more prudent if we make the last more dynamic using something like size().

                                   

                                  I shall look into this later today for you.

                                   

                                  Steve

                                  1 2 Previous Next