1 Reply Latest reply on Jul 5, 2013 7:18 AM by Jonathan Drummey

    Table with last non null value for each measure

    gioel.molinari

      I have a bunch of measures that are time series, and they have different ending dates. I would like to create a table that looks like this:

       

                                    Last value

      MeasureName1          13

      MeasureName2          18

      MeasureName3          20

       

      I tried using a calc field with Last() = 0 and then setting a filter on this = True, but what happens is I get a table that has column equal to the last date in the dataset but shows null values for the measures that did not have a value on this date. Is there any way to do this (preferably without having a calc field for each measure as I expect to have ~25 of them in the table as rows)?

       

      Thanks!

        • 1. Re: Table with last non null value for each measure
          Jonathan Drummey

          Hi,

           

          See the attached. There are a couple of complexities to this solution. First of all, Tableau does not partition table calculations on the Measure Names convenience dimension, so we have to un-pivot the data so there is a dimension to identify the measure. I used Custom SQL to do this, you could use something else. (In general, Tableau works better with "tall" data rather than "wide data).

           

          The second part is that Tableau tries to make life easier by automatically padding out data when we use a table calculation with a compute using of the date. Most of the time this works in our favor, this is one of those times when it doesn't. So, rather than using the date I used a string field based on the date.

           

          Hope this helps!

           

          Jonathan